Zmniejszyliśmy liczbę zapisów jednak nadal problem występuje. W tym miesiącu wystąpiła jedna sytuacja, w której został wyczyszczony plik tekstowy z karty SD. Przy czym, doprecyzowując poprzednie wiadomości (pośredniczę tylko w tworzeniu, nie jestem autorem programu), uszkodzeniom ulegają tylko pliki (odczytu uprawnień oraz zapisu logów). Karty SD pozostają jednak sprawne.
Kod:
//MCP230117
#include <Wire.h> // must be included here so that Arduino library object file references work
#include "Adafruit_MCP23017.h"// Instance of MCP23017 library
Adafruit_MCP23017 mcp;
//RFID
#include <SoftwareSerial.h>
#include <SPI.h>
SoftwareSerial Rfid(2, 0);
String readcard, readcard2;
char readcard_char[16];
int fe,fe1 = 0;
//SD
#include <SD.h>
const int chipSelect = D8;
File myFile;
String karty_boss_string = "\0";
bool u = false;
bool doVariableUpdate = true;
//CLOCK
#include "RTClib.h"
RTC_DS1307 rtc;
//wifi
#include <ESP8266WiFi.h>
//config.ini
String loginMySQL, hasloMySQL, STASSID, STAPSK, bazaMySQL, cronLogi, cronRFID, wozekID, cronMySQL;
//webserver
#include <ESP8266WebServer.h>
ESP8266WebServer webServer;
//RGB LED
// constants won't change. Used here to set a pin number:
const int redpin = 2;// the number of the LED pin
const int greenpin = 3;// the number of the LED pin
const int bluepin = 1;// the number of the LED pin
//mysql
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
IPAddress server_addr;
char hostName[] = "database.for.arduino.io"; // change to your server’s hostname/URL
char user[65]; // MySQL user login username
char pass[65]; // MySQL user login password
char default_db[65];
int forkliftID;
WiFiClient client; // Use this for WiFi instead of EthernetClient
MySQL_Connection conn((Client *)&client);
WiFiServerSecure server(443);
MySQL_Cursor cur = MySQL_Cursor(&conn);
// Sample query
char query[] = "SELECT date_time FROM workers_last_update WHERE 1 ORDER BY id DESC LIMIT 1";
char query1[] = "SELECT a.rcp_card FROM workers AS a INNER JOIN forklift_permission AS b ON b.workers_id=a.id WHERE b.permission = 1 AND b.forklift_id = %d";
char query2[] = "SELECT arduino_id FROM forklift_logs WHERE forklift_id = %d ORDER BY id DESC LIMIT 1";
char query3[] = "SELECT CURRENT_TIME";
char query4[] = "SELECT CURDATE()";
char InsertQuery[] = "INSERT INTO forklift_logs (date_in, rcp_card) VALUES ('0000-00-00 00:00:00', '00000000')";
String worker_last_update, worker_new_permission, logi_last_update, logi, idLog, rcpLog, dateLog, tmp_read_string, rcpApprove, unixTime, unixDate;
int indexFirst, indexSecond, indexThird, countToTen, counterLogi;
byte RFIDavaible = 1, blinkIndex = 0, stopBlink = 0, stopWiFi = 0;
String last_boss_datetime = "";
String last_boss_datetime2 = "";
//cron
unsigned long previousMillis = 0; // last time update
long interval = 60000; // interval at which to do something (milliseconds)15 sekund=15000
unsigned long previousMillisRFID = 0;
long intervalRFID = 5000;
unsigned long previousMillisBlink = 0;
long intervalBlink = 250;
unsigned long previousMillisWiFi = 0;
long intervalWiFi = 15000;
//mDNS
#include <ESP8266mDNS.h>
bool ifStand = false;
void setup() {
//mpc23017
mcp.begin();
// przekaźnik
mcp.pinMode(0, OUTPUT);
mcp.digitalWrite(0, LOW);
//LEDy
mcp.pinMode(greenpin, OUTPUT);
mcp.pinMode(redpin, OUTPUT);
mcp.pinMode(bluepin, OUTPUT);
mcp.digitalWrite(greenpin, LOW);
mcp.digitalWrite(redpin, LOW);
mcp.digitalWrite(bluepin, LOW);
//RFID
Serial.begin(9600); // Initialize serial communications with the PC
// Communication to the RFID reader
Rfid.begin(9600);
ReadLastFromSD("config.ini");
delay(200);
STASSID.trim();
STAPSK.trim();
const char* ssid = STASSID.c_str();
const char* password = STAPSK.c_str();
//wifi
Serial.print("Connecting to ");
Serial.println(ssid);
WiFi.begin(ssid, password);
//webserver
webServer.on("/updateBoss",updateBossRemotely);
webServer.on("/",returnIP);
webServer.begin();
//CLOCK
rtc.begin();
//TryWiFiMySQLntp();
}
void loop(){
//mDNS
MDNS.update();
//First Run
if(doVariableUpdate == true){
ReadSomethingFromSD("BOSS.TXT");
ReadLastFromSD("LASTLOGI.TXT");
doVariableUpdate = false;
}
//cron
unsigned long currentMillis = millis();
if(currentMillis - previousMillis > interval) {
previousMillis = currentMillis;
if(WiFi.status() == WL_CONNECTED){
DoUpdateAtBackground();
}
}
if(stopBlink == 0){
//webserver
webServer.handleClient();
if(currentMillis - previousMillis > interval) {
previousMillis = currentMillis;
if(WiFi.status() == WL_CONNECTED){
TryWiFiMySQLntp();
DisplayInfo(1, 0, 0, 0, true);
DoBossUpdate();
DoLogiUpdate();
DisplayInfo(0, 0, 0, 0, true);
}
}
if(currentMillis - previousMillisRFID > intervalRFID) {
previousMillisRFID = currentMillis;
RFIDavaible = 1;
}
/*if(currentMillis - previousMillisGreen > intervalGreen) {
previousMillisGreen = currentMillis;
mcp.digitalWrite(0, LOW);
mcp.digitalWrite(greenpin, LOW);
stopBlink = 0;
}*/
if(currentMillis - previousMillisBlink > intervalBlink) {
previousMillisBlink = currentMillis;
if(stopBlink == 0){
if(blinkIndex == 0){
mcp.digitalWrite(bluepin, LOW);
blinkIndex = 1;
}else{
mcp.digitalWrite(bluepin, HIGH);
blinkIndex = 0;
}
}
}
if(currentMillis - previousMillisWiFi > intervalWiFi) {
previousMillisWiFi = currentMillis;
if(WiFi.status() != WL_CONNECTED){
TryWiFiMySQLntp();
}
}
if(Rfid.available() > 0){
if(RFIDavaible == 1){
//CLOCK
DateTime now = rtc.now();
//RFID as long as there is data available...
readcard_char[fe] = (char)Rfid.read();
if(fe > 11){
RFIDavaible = 0;
previousMillisRFID = currentMillis;
readcard = "";
Serial.print("Odbito karte: ");
while(fe1 < strlen (readcard_char)){
mcp.digitalWrite(bluepin, HIGH);
if((fe1 > 2) && (fe1 < 11)){
readcard += readcard_char[fe1];
}
fe1 += 1;
}
Serial.println(readcard);
if(last_boss_datetime == ""){
ReadLastFromSD("LASTLOGI.TXT");
}
counterLogi = last_boss_datetime.toInt()+1;
readcard2 = counterLogi;
readcard2 += "|";
printDateTime(now);//odczyt bieżącego czasu
readcard2 += "|";
readcard2 += readcard;
if(karty_boss_string == "\0"){
ReadSomethingFromSD("BOSS.TXT");
}
if(karty_boss_string.indexOf(readcard) > -1){
u = true;
}
if(u == true){
Serial.println("uu szefuncio");
DisplayInfo(0, 0, 1, 1, true);
}else{
Serial.println("intruz!");
DisplayInfo(1, 0, 0, 0, true);
}
if(u == true){
readcard2 += "-1";
}else{
readcard2 += "-0";
}
SaveSomethingIntoSD("LOGI.TXT", readcard2);
SaveSomethingIntoSD("LASTLOGI.TXT", String(counterLogi));
readcard2 = "";
readcard = "";
fe = 0;
readcard = readcard_char;
readcard_char[0] = (char)0;
fe1 = 0;
last_boss_datetime = "";
return;
}
fe=fe+1;
delay (10);
}else{
readcard_char[0] = (char)Rfid.read();
readcard_char[0] = (char)0;
}
}
}
}
void SaveSomethingIntoSD(String file_name, String text_to_save){
if (!SD.begin(chipSelect)) {
Serial.println("SD failed!");
return; //while (1);
}
if(file_name == "LASTLOGI.TXT"){
SD.remove(file_name);
}
myFile = SD.open(file_name, FILE_WRITE);
// if the file opened okay, write to it:
if (myFile) {
myFile.println(text_to_save);
// close the file:
myFile.close();
} else {
// if the file didn't open, print an error:
Serial.println("error opening ");
Serial.println(file_name);
}
}
void ReadSomethingFromSD(String file_name){
if (!SD.begin(chipSelect)) {
Serial.println("SD read failed!");
return; //while (1);
}
karty_boss_string = "\0";
char tmp_read;
// re-open the file for reading:
myFile = SD.open(file_name);
//ODCZYT KART KTORE MAJA DOSTEP
if (myFile) {
while((myFile.available() > 0) && (u == false)) {
tmp_read = myFile.read();
karty_boss_string += (char)tmp_read;
}
myFile.close();
} else {
Serial.println("error boss.txt");
}
}
//LEDY RGB i przekaznik
void DisplayInfo(int RedPinState, int GreenPinState, int BluePinState, int if_open, bool if_card)
{
unsigned long currentMillis = millis();
//diody
if(RedPinState == 1){
mcp.digitalWrite(bluepin, LOW);
mcp.digitalWrite(redpin, HIGH);
delay(1000);
mcp.digitalWrite(redpin, LOW);
}else{
mcp.digitalWrite(redpin, LOW);
}
if(GreenPinState == 1){
mcp.digitalWrite(greenpin, HIGH);
delay(1000);
mcp.digitalWrite(greenpin, LOW);
}else{
mcp.digitalWrite(greenpin, LOW);
}
if(BluePinState == 1){
mcp.digitalWrite(bluepin, HIGH);
delay(1000);
mcp.digitalWrite(bluepin, LOW);
}else{
mcp.digitalWrite(bluepin, LOW);
}
if(if_open == 1){
Serial.println("BZZZT");
mcp.digitalWrite(0, HIGH);
mcp.digitalWrite(greenpin, HIGH);
stopBlink = 1;
//previousMillisGreen = currentMillis;//gaśnie zielony po 5 sekundach
mcp.digitalWrite(bluepin, LOW);
mcp.digitalWrite(redpin, LOW);
}else{
}
}
//CLOCK
#define countof(a) (sizeof(a) / sizeof(a[0]))
void printDateTime(const DateTime& dt)
{
char datestring[20];
snprintf_P(datestring,
countof(datestring),
PSTR("%04u-%02u-%02u %02u:%02u:%02u"),
dt.year(),//nie wiem czemu dodaje 30 lat xD
dt.month(),
dt.day(),
dt.hour(),
dt.minute(),
dt.second() );
readcard2 += datestring;
}
void DoBossUpdate(){
ReadSomethingFromMySQL(1);
if(last_boss_datetime2 == ""){
ReadLastFromSD("LASTBOSS.TXT");
}
if(last_boss_datetime2 < worker_last_update){
//AKTUALIZACJA UPRAWNIEŃ
ReadSomethingFromMySQL(2);//pobierz pracownikow do aktualizacji
SavePermissionIntoSD("BOSS.TXT", worker_new_permission);
SavePermissionIntoSD("LASTBOSS.TXT", worker_last_update);
worker_last_update = "";
last_boss_datetime = "";
}
worker_last_update = "";
last_boss_datetime = "";
doVariableUpdate = true;
}
void DoLogiUpdate(){
ReadSomethingFromMySQL(3);
if(last_boss_datetime == ""){
ReadLastFromSD("LASTLOGI.TXT");
}
if(last_boss_datetime.toInt() > logi_last_update.toInt()){
//AKTUALIZACJA LOGÓW
ReadLogiFromSD("LOGI.TXT");
logi_last_update = "";
last_boss_datetime = "";
}
logi_last_update = "";
last_boss_datetime = "";
}
void ReadLogiFromSD(String file_name){
countToTen = 0;
logi = "INSERT INTO forklift_logs (date_in, rcp_card, arduino_id, forklift_id, approved) VALUES ";
if (!SD.begin(chipSelect)) {
Serial.println("SD failed!");
return;
}
char tmp_read;
// re-open the file for reading:
myFile = SD.open(file_name);
//ODCZYT
if (myFile) {
// read from the file until there's nothing else in it:
while(myFile.available() > 0) {
idLog, dateLog, rcpLog, rcpApprove = "";
indexFirst, indexSecond, indexThird = 0;
tmp_read = myFile.read();
if(tmp_read != '\n'){
tmp_read_string += String(tmp_read);
}
if(tmp_read == '\n'){
indexFirst = tmp_read_string.indexOf("|");
indexSecond = tmp_read_string.lastIndexOf("|");
indexThird = tmp_read_string.lastIndexOf("-");
idLog = tmp_read_string.substring(0, indexFirst);
dateLog = tmp_read_string.substring(indexFirst+1, indexSecond);
rcpLog = tmp_read_string.substring(indexSecond+1, indexThird);
rcpApprove = tmp_read_string.substring(indexThird+1);
if((logi_last_update.toInt()) < (idLog.toInt())){
logi += "('"+dateLog+"','"+rcpLog+"','"+idLog+"','"+forkliftID+"','"+rcpApprove+"'),";
if(countToTen < 10){
countToTen=countToTen+1;
}else{
logi = logi.substring(0, logi.length()-1);
SaveLogsIntoMySQL(logi);
countToTen = 0;
logi = "INSERT INTO forklift_logs (date_in, rcp_card, arduino_id, forklift_id, approved) VALUES ";
}
}
tmp_read_string = "";
}
}
logi = logi.substring(0, logi.length()-1);
SaveLogsIntoMySQL(logi);
myFile.close();
} else {
Serial.println("error logi.txt");
}
}
void ReadLastFromSD(String file_name){
if (!SD.begin(chipSelect)) {
Serial.println("SD failed!");
return;
}
byte indexIni = 0, i;
char tmp_read;
// re-open the file for reading:
myFile = SD.open(file_name);
//ODCZYT
if (myFile) {
// read from the file until there's nothing else in it:
while(myFile.available()) {
tmp_read = myFile.read();
if(file_name == "LASTLOGI.TXT"){
last_boss_datetime += (char)tmp_read;
}
if(file_name == "LASTBOSS.TXT"){
last_boss_datetime2 += (char)tmp_read;
}
//wczytanie konfiguracji z pliku *.ini
if(file_name == "config.ini"){
if(tmp_read == 10){
tmp_read = (char)0;
indexIni = indexIni+1;
}
if((tmp_read > 0) && (tmp_read != 10)){
if(indexIni == 0){
STASSID += (char)tmp_read;
}
if(indexIni == 1){
STAPSK += (char)tmp_read;
}
if(indexIni == 2){
loginMySQL += (char)tmp_read;
}
if(indexIni == 3){
hasloMySQL += (char)tmp_read;
}
if(indexIni == 4){
bazaMySQL += (char)tmp_read;
}
if(indexIni == 5){
cronLogi += (char)tmp_read;
}
if(indexIni == 6){
cronRFID += (char)tmp_read;
}
if(indexIni == 7){
wozekID += (char)tmp_read;
}
if(indexIni == 8){
cronMySQL += (char)tmp_read;
}
}
}
}
if(file_name == "config.ini"){
int str_len;
loginMySQL.trim();
str_len = loginMySQL.length()+1;
loginMySQL.toCharArray(user, str_len);
hasloMySQL.trim();
str_len = hasloMySQL.length()+1;
hasloMySQL.toCharArray(pass, str_len);
bazaMySQL.trim();
str_len = bazaMySQL.length()+1;
bazaMySQL.toCharArray(default_db, str_len);
interval = cronLogi.toInt();
intervalRFID = cronRFID.toInt();
forkliftID = wozekID.toInt();
intervalWiFi = cronMySQL.toInt();
}
myFile.close();
} else {
Serial.println("error lastboss.txt");
}
}
void SavePermissionIntoSD(String file_name, String text_to_save){
if (!SD.begin(chipSelect)) {
Serial.println("SD failed!");
return; //while (1);
}
SD.remove(file_name);
myFile = SD.open(file_name, FILE_WRITE);
// if the file opened okay, write to it:
if (myFile) {
myFile.println(text_to_save);
// close the file:
myFile.close();
} else {
// if the file didn't open, print an error:
Serial.println("error opening ");
Serial.println(file_name);
}
}
void ReadSomethingFromMySQL(int withQuery){
row_values *row = NULL;
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Execute the query
if(withQuery == 1){
cur_mem->execute(query);
}
if(withQuery == 2){
worker_new_permission = "";
char query69[150];
sprintf(query69, query1, forkliftID);
cur_mem->execute(query69);
}
if(withQuery == 3){
char query68[150];
sprintf(query68, query2, forkliftID);
cur_mem->execute(query68);
}
if(withQuery == 4){
cur_mem->execute(query3);
}
if(withQuery == 5){
cur_mem->execute(query4);
}
// Fetch the columns (required) but we don't use them.
column_names *columns = cur_mem->get_columns();
// Read the row (we are only expecting the one)
do {
row = cur_mem->get_next_row();
if (row != NULL) {
if(withQuery == 1){
worker_last_update = row->values[0];
}
if(withQuery == 2){
worker_new_permission += row->values[0];
worker_new_permission += ",";
}
if(withQuery == 3){
logi_last_update = row->values[0];
}
if(withQuery == 4){
unixTime = row->values[0];
}
if(withQuery == 5){
unixDate = row->values[0];
}
}
} while (row != NULL);
// Deleting the cursor also frees up memory used
delete cur_mem;
}
void SaveLogsIntoMySQL(String InsertQuery){
int str_len = InsertQuery.length() + 1;
char querry[str_len];
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
InsertQuery.toCharArray(querry, str_len);
cur_mem->execute(querry);
delete cur_mem;
}
void TryWiFiMySQLntp(){
if(WiFi.status() != WL_CONNECTED){
Serial.println("WiFi NOT connected");
}else{
Serial.println("");
Serial.println("WiFi connected");
Serial.println(WiFi.localIP());
//mDNS
if(ifStand == false){
if (!MDNS.begin("forklift")) {
Serial.println("Error setting up MDNS responder!");
while (1) {
delay(1000);
}
}
MDNS.addService("http", "tcp", 80);
ifStand = true;
}
//mysql
WiFi.hostByName(hostName, server_addr);
Serial.println("Connecting...");
IPAddress server_addr(192,168,0,82);
if (conn.connect(server_addr, 3306, user, pass, default_db)) {
delay(1000);
Serial.println("Connected");
}else{
Serial.println("Connection failed.");
}
//CLOCK
ReadSomethingFromMySQL(4);
ReadSomethingFromMySQL(5);
String tmpDateTime = unixDate+"T"+unixTime;
rtc.adjust(DateTime(tmpDateTime.c_str()));
Serial.print(unixDate);
Serial.print(" ");
Serial.println(unixTime);
//conn.close();
}
}
void updateBossRemotely(){
TryWiFiMySQLntp();
DoBossUpdate();
webServer.send(200, "text/plain", "Remote update will force");
}
void returnIP(){
IPAddress ip = WiFi.localIP();
String ipStr = String(ip[0]) + '.' + String(ip[1]) + '.' + String(ip[2]) + '.' + String(ip[3]);
String tmptext = "<html>Yours forklift device is avaiable behind this address: <b>"+ipStr+"</b></html>";
webServer.send(200, "text/html", tmptext);
}
void DoUpdateAtBackground(){
TryWiFiMySQLntp();
DoBossUpdate();
DoLogiUpdate();
}
Do głowy prszyszło mi obejście problemu (jednak nie rozwiązanie), aby co X czasu tworzyła się kopia plików tekstowych na karcie SD i w przypadku wyczyszczenia plików głównych, zostały one wczytane z kopii. Jednak jest to rozwiązanie dość okrężne.