The idea is to monitor the amount of liquid dispensed and transfer the data to a database.

The data must be able to be monitored via a web interface.

The project contains a simple setup:

Wifi module - Node MCU
Virtual Server - Wampserver, MySQL
Soap-dispenser Incl. Sensor, DC-motor, Battery-holder 4xAA and board.
Node-Red.
Cork from a wine bottle.

Step 1: Get Started

1. Install Node-Red on your computer.

2. Install "dashboard, node-red-node-mysql and node-red-contrib-mqtt-broker" (Node-Red -Library)

3. Go in to Manage Palette >Then click on install

4.Then search after the modules

5.Install Wampserver on your computer"

6. Create a tabel in MySQL. Write the titles of the thinges you will like to have ex,"Bottle Id, CL, Label, Date" (See pic.)

7. Insert MySQL block into Node-Red

8. The block in Node-Red "Mysql" has to containe the name of our SQL-database. In our case "acl_disp". Furthermore a function before the "mysql" block containing a SQL query needs to be inserted. (See pic.)

Node-red: 127.0.0.1:1880
Dashboard: 127.0.0.1:1880/ui
MySql: 127.0.0.1/phpmyadmin User: root

Step 2: Mechanical Parts.

The dispenser is an old soap-dispenser you will find at any public toliet or restroom. (Don't steal it)
It incl. Battery-holder, DC-motor, motion sensor and board.
Cork. Drill hole through 6 mm. Then it fits the bottle and the white mouthpiece.
I/O-liste. GND, 3V, 1 PWM port for Input.

Step 3: Data From DC Motor.

Add and connect two wires to the DC motor.
One to GND and one to D1 (PWM) on the CMU.

Step 4: Set Up a Mosca MQTT Broker.

Set up a Mosca MQTT broker.

How to Install and Run The Mosca MQTT Broker on Node-Red.

Rather than use an external broker like mosquitto with node-red you can install Mosca which is a MQTT broker written in node.js. Because Mosca isn’t part of the core nodes you need to install it using the npm package manager or through the Node-red Admin control panel.
Mosca Install Using Node-red Admin. Go to Settings > Manage Pallette.
Click on the install tab > search for node-red-contrib-mqtt.
After the install you should find a new node called mosca under the input categories.
Drag and drop on the Canvas to use. Mosca Node Configuration The node properties is a single page and with very few settings. You need to set the ports for standard MQTT and MQTT over websockets.

Step 5: NODEMCU Code

<p>/*The connection code is taken from: <a href="https://randomnerdtutorials.com/esp8266-and-node-red-with-mqtt/"> https://randomnerdtutorials.com/esp8266-and-node-...<br></a>and alteret to our purpose.</p><p>This code is a dispenser that operates using a PIR sensor.<br>  When the sensor is HIGH it sends a topic to node-red witch sends a SQL query to a MySQL database on a WAMP server.  Then MySQL returns the result to the nodemcu through node-red.  The nodemcu alter the result and send the updated data back through node-red to MySQL.*/</p>

#include <ESP8266WiFI.h>#include <PubSubClient.h><br>

// Connect to the WiFiconst char* ssid = "Insert your SSID";const char* password = "Insert your password";const char* mqtt_server = "insert the IP for the MQTT broker";

//millisunsigned long currentDelayMillis;const unsigned long delayPeriod = 2000;unsigned long startDelayMillis;

// Initializes the espClient.//You should change the espClient name if you have multiple ESPs running in your home automation systemWiFiClient espClient;PubSubClient client(espClient);

// Global variablesconst int disp_1 = 4;bool flag_1;bool warningLow_1;char buf_1[4];int clDisp_1;int tempDisp_1;

// This functions connects your ESP8266 to your routervoid setup_wifi() {  delay(10);  // We start by connecting to a WiFi network  Serial.println();  Serial.print("Connecting to ");  Serial.println(ssid);  WiFi.begin(ssid, password);  while (WiFi.status() != WL_CONNECTED) {    delay(500);    Serial.print(".");  }  Serial.println("");  Serial.print("WiFi connected - ESP IP address: ");  Serial.println(WiFi.localIP());}

/*This functions is executed when some device publishes a message to a topic that your ESP8266 is subscribed to  Change the function below to add logic to your program, so when a device publishes a message to a topic that  your ESP8266 is subscribed you can actually do something*/void callback(String topic, byte* message, unsigned int length) {  Serial.print("Message arrived on topic: ");  Serial.print(topic);  Serial.print(". Message: ");  String messageTemp;

  for (int i = 0; i < length; i++) {    Serial.print((char)message[i]);    messageTemp += (char)message[i];  }  Serial.println();

  /*If a message is received on the topic LED, you check if the message is either on or off.    Turns the LED according to the message.    This is only made for testing purpose to simulate an input from a sensor.*/  /*if (topic == "LED") {    if (messageTemp == "on") {      digitalWrite(disp_1, HIGH);    }    else if (messageTemp == "off") {      digitalWrite(disp_1, LOW);    }  }*/

  /*Getting the sql query result. Manipulate the sting to give the CL number only.    Convert to int and substract 2 CL. Convert back to char and check if CL is under 6.    If CL is under 6 then give a warning in node-red to change the bottle.    For more than one bottle - copy this topic and the warning topic - then change topics and global variables.    make sure to do the same in void loop and void setup*/  if (topic == "RCV_DISP_1") {    int findSemi = messageTemp.indexOf(':');    int findBrc = messageTemp.lastIndexOf('}');    String getDigit = messageTemp.substring(findSemi + 1, findBrc);    tempDisp_1 = getDigit.toInt();    clDisp_1 = tempDisp_1 - 2;    itoa(clDisp_1, buf_1, 10);

    if (tempDisp_1 <= 6) {      warningLow_1 = HIGH;      flag_1 = HIGH;    }    else if (tempDisp_1 > 6) {      warningLow_1 = LOW;      flag_1 = HIGH;    }    else if (tempDisp_1 <= 0) {      warningLow_1 = HIGH;      flag_1 = LOW;    }  }  //reset warning  if (topic == "warningRET_1") {    warningLow_1 = LOW;  }  Serial.println();}

// This functions reconnects the ESP8266 to the MQTT brokervoid reconnect() {  // Loop until we're reconnected  while (!client.connected()) {    Serial.print("Attempting MQTT connection...");    // Attempt to connect    /*      YOU MIGHT NEED TO CHANGE THIS LINE, IF YOU'RE HAVING PROBLEMS WITH MQTT MULTIPLE CONNECTIONS      To change the ESP device ID, you will have to give a new name to the ESP8266.      Here's how it looks:       if (client.connect("ESP8266Client")) {      You can do it like this:       if (client.connect("ESP1_Office")) {      Then, for the other ESP:       if (client.connect("ESP2_Garage")) {      That should solve your MQTT multiple connections problem    */    if (client.connect("ESP8266Client")) {      Serial.println("connected");      // Subscribe or resubscribe to a topic      // You can subscribe to more topics      client.subscribe("LED");      client.subscribe("DISP_1");      client.subscribe("RCV_DISP_1");      client.subscribe("chDISP_1");      client.subscribe("warningLOW_1");      client.subscribe("warningRET_1");    } else {      Serial.print("failed, rc=");      Serial.print(client.state());      Serial.println(" try again in 5 seconds");      // Wait 5 seconds before retrying      delay(5000);    }  }}

void setup() {  //Pinmode disp_1 is an output when testing from node-red using topic LED    pinMode(disp_1, INPUT);    flag_1 = LOW;  warningLow_1 = LOW;  Serial.begin(115200);  setup_wifi();  client.setServer(mqtt_server, 1883);  client.setCallback(callback);  startDelayMillis = millis();}//ensures connectionvoid loop() {

  if (!client.connected()) {    reconnect();  }  if (!client.loop()) {    client.connect("ESP8266Client");  }

  /*The millis ensure that the client.publish isn't sending more than once every time the sensor is 1.    It takes about 2 seconds to get the alcohol from the dispencer. So the millis delay is 2000 ms*/  if (digitalRead(disp_1) == 1) {    currentDelayMillis = millis();    if (currentDelayMillis - startDelayMillis >= delayPeriod) {      client.publish("DISP_1", "1");    }    startDelayMillis = currentDelayMillis;  }

  /*Gets boolean from callback and ensures that there is alcohol in the dispenser.    Then publish the new ammount of alcohol.         Publish low ammount warning*/  if ((flag_1 == HIGH) && (tempDisp_1 > 0)) {    client.publish("chDISP_1", buf_1);    flag_1 = LOW;  }  if (warningLow_1 == HIGH) {    client.publish("warningLOW_1", "1");  }}