Fri, 25 Jan 2019 20:24:00 +0100
Hops inventory disable filter menu on some fields. Friendy display of stock inventory.
/** * @file mysql.c * @brief MySQL/MariaDB access. * @author Michiel Broek <mbroek at mbse dot eu> * * Copyright (C) 2018-2019 * * This file is part of the bms (Brewery Management System) * * This is free software; you can redistribute it and/or modify it * under the terms of the GNU General Public License as published by the * Free Software Foundation; either version 2, or (at your option) any * later version. * * bms is distributed in the hope that it will be useful, but * WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * General Public License for more details. * * You should have received a copy of the GNU General Public License * along with ThermFerm; see the file COPYING. If not, write to the Free * Software Foundation, 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA. */ #include "bms.h" #include "xutil.h" #include "mysql.h" #include "nodes.h" MYSQL *con = NULL; MYSQL_RES *res_set; MYSQL_ROW row; extern sys_config Config; extern sys_node_list *nodes; extern sys_fermenter_list *fermenters; extern int debug; time_t datetime_to_time_t(char *dt_string) { struct tm tm; memset(&tm, 0, sizeof(struct tm)); tm.tm_year = atoi(strtok(dt_string, "-")) - 1900; tm.tm_mon = atoi(strtok(NULL, "-")) - 1; tm.tm_mday = atoi(strtok(NULL, " ")); tm.tm_hour = atoi(strtok(NULL, ":")); tm.tm_min = atoi(strtok(NULL, ":")); tm.tm_sec = atoi(strtok(NULL, "\0")); tm.tm_isdst = 1; return mktime(&tm); } int bms_mysql_init(void) { sys_node_list *node, *tmpp; sys_fermenter_list *fermenter, *tmpf; int ncnt = 0, fcnt = 0; con = mysql_init(NULL); if (con == NULL) { syslog(LOG_NOTICE, "MySQL: mysql_init() failed"); return 1; } if (mysql_real_connect(con, Config.mysql_host, Config.mysql_user, Config.mysql_pass, Config.mysql_database, Config.mysql_port, NULL, 0) == NULL) { syslog(LOG_NOTICE, "MySQL: mysql_real_connect() %s", mysql_error(con)); return 2; } syslog(LOG_NOTICE, "MySQL: connected to %s:%d database %s", Config.mysql_host, Config.mysql_port, Config.mysql_database); syslog(LOG_NOTICE, "MySQL: %s server info: %s", mysql_get_host_info(con), mysql_get_server_info(con)); if (debug) fprintf(stdout, "MySQL: connected\n"); /* * Restore nodes from the database */ if (mysql_query(con, "SELECT * FROM mon_nodes")) { syslog(LOG_NOTICE, "MySQL: SELECT * FROM mon_nodes error %u (%s))", mysql_errno(con), mysql_error(con)); } else { res_set = mysql_store_result(con); if (res_set == NULL) { syslog(LOG_NOTICE, "MySQL: mysq_store_result error %u (%s))", mysql_errno(con), mysql_error(con)); } else { // Process results while ((row = mysql_fetch_row(res_set)) != NULL) { node = (sys_node_list *)malloc(sizeof(sys_node_list)); memset(node, 0, sizeof(sys_node_list)); node->next = NULL; node->uuid = xstrcpy(row[1]); node->node = xstrcpy(row[2]); node->online = false; // Will be set using MQTT node->group_id = xstrcpy(row[4]); node->hardwaremake = xstrcpy(row[5]); node->hardwaremodel = xstrcpy(row[6]); node->os = xstrcpy(row[7]); node->os_version = xstrcpy(row[8]); node->firmware = xstrcpy(row[9]); node->firstseen = datetime_to_time_t(row[10]); node->lastseen = datetime_to_time_t(row[11]); node->temperature = atof(row[12]); node->humidity = atof(row[13]); node->barometer = atof(row[14]); node->gps_latitude = atof(row[15]); node->gps_longitude = atof(row[16]); node->gps_altitude = atof(row[17]); node->net_address = xstrcpy(row[18]); node->net_ifname = xstrcpy(row[19]); node->net_rssi = atoi(row[20]); if (nodes == NULL) { nodes = node; } else { for (tmpp = nodes; tmpp; tmpp = tmpp->next) { if (tmpp->next == NULL) { tmpp->next = node; break; } } } ncnt++; } mysql_free_result(res_set); } } if (mysql_query(con, "SELECT * FROM mon_fermenters")) { syslog(LOG_NOTICE, "MySQL: SELECT * FROM mon_fermenters error %u (%s))", mysql_errno(con), mysql_error(con)); } else { res_set = mysql_store_result(con); if (res_set == NULL) { syslog(LOG_NOTICE, "MySQL: mysq_store_result error %u (%s))", mysql_errno(con), mysql_error(con)); } else { while ((row = mysql_fetch_row(res_set)) != NULL) { fermenter = (sys_fermenter_list *)malloc(sizeof(sys_fermenter_list)); memset(fermenter, 0, sizeof(sys_fermenter_list)); fermenter->next = NULL; fermenter->uuid = xstrcpy(row[1]); fermenter->alias = xstrcpy(row[2]); fermenter->node = xstrcpy(row[3]); fermenter->online = false; // Will be set later fermenter->beercode = xstrcpy(row[5]); fermenter->beername = xstrcpy(row[6]); fermenter->beeruuid = xstrcpy(row[44]); if (strlen(row[7])) { fermenter->air_address = xstrcpy(row[7]); fermenter->air_state = xstrcpy(row[8]); fermenter->air_temperature = atof(row[9]); } if (strlen(row[10])) { fermenter->beer_address = xstrcpy(row[10]); fermenter->beer_state = xstrcpy(row[11]); fermenter->beer_temperature = atof(row[12]); } if (strlen(row[13])) { fermenter->chiller_address = xstrcpy(row[13]); fermenter->chiller_state = xstrcpy(row[14]); fermenter->chiller_temperature = atof(row[15]); } if (strlen(row[16])) { fermenter->heater_address = xstrcpy(row[16]); fermenter->heater_state = atoi(row[17]); fermenter->heater_usage = atoi(row[18]); } if (strlen(row[19])) { fermenter->cooler_address = xstrcpy(row[19]); fermenter->cooler_state = atoi(row[20]); fermenter->cooler_usage = atoi(row[21]); } if (strlen(row[22])) { fermenter->fan_address = xstrcpy(row[22]); fermenter->fan_state = atoi(row[23]); fermenter->fan_usage = atoi(row[24]); } if (strlen(row[25])) { fermenter->light_address = xstrcpy(row[25]); fermenter->light_state = atoi(row[26]); fermenter->light_usage = atoi(row[27]); } if (strlen(row[28])) { fermenter->door_address = xstrcpy(row[28]); fermenter->door_state = atoi(row[29]); } if (strlen(row[30])) { fermenter->psu_address = xstrcpy(row[30]); fermenter->psu_state = atoi(row[31]); } fermenter->mode = xstrcpy(row[32]); fermenter->alarm = atoi(row[33]); fermenter->setpoint_high = atof(row[34]); fermenter->setpoint_low = atof(row[35]); if (strlen(row[36])) { fermenter->profile_uuid = xstrcpy(row[36]); fermenter->profile_name = xstrcpy(row[37]); fermenter->profile_state = xstrcpy(row[38]); fermenter->profile_percent = atoi(row[39]); fermenter->profile_inittemp_high = atof(row[40]); fermenter->profile_inittemp_low = atof(row[41]); fermenter->profile_steps = xstrcpy(row[42]); } fermenter->stage = xstrcpy(row[43]); if (fermenters == NULL) { fermenters = fermenter; } else { for (tmpf = fermenters; tmpf; tmpf = tmpf->next) { if (tmpf->next == NULL) { tmpf->next = fermenter; break; } } } fcnt++; } mysql_free_result(res_set); } } syslog(LOG_NOTICE, "MySQL: loaded %d nodes, %d fermenters", ncnt, fcnt); return 0; } void bms_mysql_end(void) { sys_fermenter_list *tmpf, *oldtmpf; sys_node_list *tmpn, *oldtmpn; mysql_close(con); syslog(LOG_NOTICE, "MySQL: disconnected"); if (debug) fprintf(stdout, "MySQL: disconnected\n"); for (tmpf = fermenters; tmpf; tmpf = oldtmpf) { oldtmpf = tmpf->next; if (tmpf->uuid) free(tmpf->uuid); if (tmpf->alias) free(tmpf->alias); if (tmpf->node) free(tmpf->node); if (tmpf->beercode) free(tmpf->beercode); if (tmpf->beername) free(tmpf->beername); if (tmpf->beeruuid) free(tmpf->beeruuid); if (tmpf->air_address) free(tmpf->air_address); if (tmpf->air_state) free(tmpf->air_state); if (tmpf->beer_address) free(tmpf->beer_address); if (tmpf->beer_state) free(tmpf->beer_state); if (tmpf->chiller_address) free(tmpf->chiller_address); if (tmpf->chiller_state) free(tmpf->chiller_state); if (tmpf->heater_address) free(tmpf->heater_address); if (tmpf->cooler_address) free(tmpf->cooler_address); if (tmpf->fan_address) free(tmpf->fan_address); if (tmpf->light_address) free(tmpf->light_address); if (tmpf->door_address) free(tmpf->door_address); if (tmpf->psu_address) free(tmpf->psu_address); if (tmpf->mode) free(tmpf->mode); if (tmpf->stage) free(tmpf->stage); if (tmpf->profile_uuid) free(tmpf->profile_uuid); if (tmpf->profile_name) free(tmpf->profile_name); if (tmpf->profile_state) free(tmpf->profile_state); if (tmpf->profile_steps) free(tmpf->profile_steps); free(tmpf); } for (tmpn = nodes; tmpn; tmpn = oldtmpn) { oldtmpn = tmpn->next; if (tmpn->uuid) free(tmpn->uuid); if (tmpn->node) free(tmpn->node); if (tmpn->group_id) free(tmpn->group_id); if (tmpn->hardwaremake) free(tmpn->hardwaremake); if (tmpn->hardwaremodel) free(tmpn->hardwaremodel); if (tmpn->os) free(tmpn->os); if (tmpn->os_version) free(tmpn->os_version); if (tmpn->firmware) free(tmpn->firmware); if (tmpn->net_address) free(tmpn->net_address); if (tmpn->net_ifname) free(tmpn->net_ifname); free(tmpn); } } void bms_mysql_ping(void) { } void node_mysql_insert(sys_node_list *node) { char *query = malloc(1024), first[21], last[21]; struct tm *mytime; mytime = localtime(&node->firstseen); snprintf(first, 20, "%04d-%02d-%02d %02d:%02d:%02d", mytime->tm_year + 1900, mytime->tm_mon + 1, mytime->tm_mday, mytime->tm_hour, mytime->tm_min, mytime->tm_sec); mytime = localtime(&node->lastseen); snprintf(last, 20, "%04d-%02d-%02d %02d:%02d:%02d", mytime->tm_year + 1900, mytime->tm_mon + 1, mytime->tm_mday, mytime->tm_hour, mytime->tm_min, mytime->tm_sec); snprintf(query, 1023, "INSERT INTO mon_nodes SET uuid='%s', node='%s', online='%s', group_id='%s', " \ "hardwaremake='%s', hardwaremodel='%s', os='%s', os_version='%s', firmware='%s', firstseen='%s', lastseen='%s', " \ "temperature='%.3f', humidity='%.3f', barometer='%.3f', gps_latitude='%.8f', gps_longitude='%.8f', gps_altitude='%.8f', " \ "net_address='%s', net_ifname='%s', net_rssi='%d'", node->uuid, node->node, node->online ?"Y":"N", node->group_id, node->hardwaremake, node->hardwaremodel, node->os, node->os_version, node->firmware, first, last, node->temperature, node->humidity, node->barometer, node->gps_latitude, node->gps_longitude, node->gps_altitude, node->net_address, node->net_ifname, node->net_rssi); if (mysql_query(con, query)) { syslog(LOG_NOTICE, "MySQL: INSERT INTO mon_nodes error %u (%s))", mysql_errno(con), mysql_error(con)); } else { syslog(LOG_NOTICE, "MySQL: insert new node %s", node->node); } free(query); } void node_mysql_update(sys_node_list *node) { char *query = malloc(1024), last[21]; struct tm *mytime; mytime = localtime(&node->lastseen); snprintf(last, 20, "%04d-%02d-%02d %02d:%02d:%02d", mytime->tm_year + 1900, mytime->tm_mon + 1, mytime->tm_mday, mytime->tm_hour, mytime->tm_min, mytime->tm_sec); snprintf(query, 1023, "UPDATE mon_nodes SET online='%s', hardwaremake='%s', hardwaremodel='%s', os='%s', os_version='%s', firmware='%s', lastseen='%s', " \ "temperature='%.3f', humidity='%.3f', barometer='%.3f', gps_latitude='%.8f', gps_longitude='%.8f', gps_altitude='%.8f', " \ "net_address='%s', net_ifname='%s', net_rssi='%d' WHERE uuid='%s'", node->online ?"Y":"N", node->hardwaremake, node->hardwaremodel, node->os, node->os_version, node->firmware, last, node->temperature, node->humidity, node->barometer, node->gps_latitude, node->gps_longitude, node->gps_altitude, node->net_address, node->net_ifname, node->net_rssi, node->uuid); if (mysql_query(con, query)) { syslog(LOG_NOTICE, "MySQL: UPDATE mon_nodes error %u (%s))", mysql_errno(con), mysql_error(con)); } free(query); } void node_mysql_death(char *node) { char *query = malloc(512); snprintf(query, 511, "UPDATE mon_nodes SET online='N' WHERE node='%s'", node); // printf("%s\n", query); if (mysql_query(con, query)) { syslog(LOG_NOTICE, "MySQL: UPDATE mon_nodes error %u (%s))", mysql_errno(con), mysql_error(con)); } free(query); } void fermenter_mysql_insert(sys_fermenter_list *fermenter) { char *query = malloc(2560); snprintf(query, 2559, "INSERT INTO mon_fermenters SET uuid='%s', alias='%s', node='%s', online='%s', " \ "beercode='%s', beername='%s', beeruuid='%s', " \ "air_address='%s', air_state='%s', air_temperature='%.3f', " \ "beer_address='%s', beer_state='%s', beer_temperature='%.3f', " \ "chiller_address='%s', chiller_state='%s', chiller_temperature='%.3f', " \ "heater_address='%s', heater_state='%d', heater_usage='%lu', " \ "cooler_address='%s', cooler_state='%d', cooler_usage='%lu', " \ "fan_address='%s', fan_state='%d', fan_usage='%lu', " \ "light_address='%s', light_state='%d', light_usage='%lu', " \ "door_address='%s', door_state='%d', " \ "psu_address='%s', psu_state='%d', " \ "mode='%s', alarm='%d', setpoint_high='%.3f', setpoint_low='%.3f', " \ "profile_uuid='%s', profile_name='%s', profile_state='%s', profile_percent='%d', " \ "profile_inittemp_high='%.3f', profile_inittemp_low='%.3f', profile_steps='%s', stage='%s'", fermenter->uuid, fermenter->alias, fermenter->node, fermenter->online ? "Y":"N", fermenter->beercode ? fermenter->beercode : "", fermenter->beername ? fermenter->beername : "", fermenter->beeruuid ? fermenter->beeruuid : "", fermenter->air_address ? fermenter->air_address : "", fermenter->air_state ? fermenter->air_state : "", fermenter->air_temperature, fermenter->beer_address ? fermenter->beer_address : "", fermenter->beer_state ? fermenter->beer_state : "", fermenter->beer_temperature, fermenter->chiller_address ? fermenter->chiller_address : "", fermenter->chiller_state ? fermenter->chiller_state : "", fermenter->chiller_temperature, fermenter->heater_address ? fermenter->heater_address : "", fermenter->heater_state, fermenter->heater_usage, fermenter->cooler_address ? fermenter->cooler_address : "", fermenter->cooler_state, fermenter->cooler_usage, fermenter->fan_address ? fermenter->fan_address : "", fermenter->fan_state, fermenter->fan_usage, fermenter->light_address ? fermenter->light_address : "", fermenter->light_state, fermenter->light_usage, fermenter->door_address ? fermenter->door_address : "", fermenter->door_state, fermenter->psu_address ? fermenter->psu_address : "", fermenter->psu_state, fermenter->mode, fermenter->alarm, fermenter->setpoint_high, fermenter->setpoint_low, fermenter->profile_uuid ? fermenter->profile_uuid : "", fermenter->profile_name ? fermenter->profile_name : "", fermenter->profile_state ? fermenter->profile_state : "", fermenter->profile_percent, fermenter->profile_inittemp_high, fermenter->profile_inittemp_low, fermenter->profile_steps ? fermenter->profile_steps : "", fermenter->stage); // printf("%s\n", query); if (mysql_query(con, query)) { syslog(LOG_NOTICE, "MySQL: INSERT INTO mon_fermenters error %u (%s))", mysql_errno(con), mysql_error(con)); } else { syslog(LOG_NOTICE, "MySQL: insert new fermenter %s/%s", fermenter->node, fermenter->alias); } free(query); } void fermenter_mysql_update(sys_fermenter_list *fermenter) { char *query = malloc(2560); snprintf(query, 2559, "UPDATE mon_fermenters SET online='%s', beercode='%s', beername='%s', beeruuid='%s', " \ "air_address='%s', air_state='%s', air_temperature='%.3f', " \ "beer_address='%s', beer_state='%s', beer_temperature='%.3f', " \ "chiller_address='%s', chiller_state='%s', chiller_temperature='%.3f', " \ "heater_address='%s', heater_state='%d', heater_usage='%lu', " \ "cooler_address='%s', cooler_state='%d', cooler_usage='%lu', " \ "fan_address='%s', fan_state='%d', fan_usage='%lu', " \ "light_address='%s', light_state='%d', light_usage='%lu', " \ "door_address='%s', door_state='%d', " \ "psu_address='%s', psu_state='%d', " \ "mode='%s', alarm='%d', setpoint_high='%.3f', setpoint_low='%.3f', " \ "profile_uuid='%s', profile_name='%s', profile_state='%s', profile_percent='%d', " \ "profile_inittemp_high='%.3f', profile_inittemp_low='%.3f', profile_steps='%s', stage='%s' WHERE uuid='%s'", fermenter->online ? "Y":"N", fermenter->beercode ? fermenter->beercode : "", fermenter->beername ? fermenter->beername : "", fermenter->beeruuid ? fermenter->beeruuid : "", fermenter->air_address ? fermenter->air_address : "", fermenter->air_state ? fermenter->air_state : "", fermenter->air_temperature, fermenter->beer_address ? fermenter->beer_address : "", fermenter->beer_state ? fermenter->beer_state : "", fermenter->beer_temperature, fermenter->chiller_address ? fermenter->chiller_address : "", fermenter->chiller_state ? fermenter->chiller_state : "", fermenter->chiller_temperature, fermenter->heater_address ? fermenter->heater_address : "", fermenter->heater_state, fermenter->heater_usage, fermenter->cooler_address ? fermenter->cooler_address : "", fermenter->cooler_state, fermenter->cooler_usage, fermenter->fan_address ? fermenter->fan_address : "", fermenter->fan_state, fermenter->fan_usage, fermenter->light_address ? fermenter->light_address : "", fermenter->light_state, fermenter->light_usage, fermenter->door_address ? fermenter->door_address : "", fermenter->door_state, fermenter->psu_address ? fermenter->psu_address : "", fermenter->psu_state, fermenter->mode, fermenter->alarm, fermenter->setpoint_high, fermenter->setpoint_low, fermenter->profile_uuid ? fermenter->profile_uuid : "", fermenter->profile_name ? fermenter->profile_name : "", fermenter->profile_state ? fermenter->profile_state : "", fermenter->profile_percent, fermenter->profile_inittemp_high, fermenter->profile_inittemp_low, fermenter->profile_steps ? fermenter->profile_steps : "", fermenter->stage, fermenter->uuid); // printf("%s\n", query); if (mysql_query(con, query)) { syslog(LOG_NOTICE, "MySQL: UPDATE mon_fermenters error %u (%s))", mysql_errno(con), mysql_error(con)); } free(query); } void fermenter_mysql_death(char *node, char *alias) { char *query = malloc(512); if (alias) snprintf(query, 511, "UPDATE mon_fermenters SET online='N' WHERE node='%s' and alias='%s'", node, alias); else snprintf(query, 511, "UPDATE mon_fermenters SET online='N' WHERE node='%s'", node); if (mysql_query(con, query)) { syslog(LOG_NOTICE, "MySQL: UPDATE mon_fermenters error %u (%s))", mysql_errno(con), mysql_error(con)); } free(query); }