static char *get_id(char *key, int *level){
char *ret=NULL, *tmp;
ret = SQLexec("id", key);
if( !(tmp = SQLexec("level", key)) ) *level = 0;
else *level = atoi(tmp);
free(tmp);
return ret;
}
/********************************************************************************************************************************
* int SaveDataPaketContainerToDatabase(datapaketcontainer* dpc):
* save dpc to database
********************************************************************************************************************************/
int SaveDataPaketContainerToDatabase(datapaketcontainer* dpc){
//PGresult *res;
int i;
char st[CMDBUFFER_SIZE];
PGresult *res;
char dtStr[CMDBUFFER_SIZE];
char adrStr[CMDBUFFER_SIZE];
char log[LOG_BUF_LEN];
int success = 0;
if(SQLTableExists(LDB_TBL_NAME_DATA)){
lielas_createDataTbl();
if(SQLTableExists(LDB_TBL_NAME_DATA)){
return -1;
}
}
snprintf(log, LOG_BUF_LEN, "saving values to database");
lielas_log((unsigned char*) log, LOG_LEVEL_DEBUG);
// create column string
//snprintf(column, BUFFER_SIZE, "%s.%s.%s", dpc->d->address, dpc->m->address, dpc->c->address);
for( i = 0; i < dpc->datapakets; i++){
//create column if not existing
snprintf(adrStr, CMDBUFFER_SIZE, "%s.%s.%s", dpc->dp[i]->d->mac, dpc->dp[i]->m->address, dpc->dp[i]->c->address);
if(SQLRowExists(LDB_TBL_NAME_DATA, adrStr)){
snprintf(st, CMDBUFFER_SIZE, "ALTER TABLE %s.%s ADD COLUMN \"%s\" text", LDB_TBL_SCHEMA, LDB_TBL_NAME_DATA , adrStr);
res = SQLexec(st);
PQclear(res);
}
// create datetime string
strftime(dtStr, CMDBUFFER_SIZE, "%Y-%m-%d %H:%M:%S", dpc->dp[i]->dt);
//check, if datetime-row exists
if(SQLCellExists(LDB_TBL_NAME_DATA, "datetime", dtStr)){
//creat new row
snprintf(st, CMDBUFFER_SIZE, "INSERT INTO %s.%s ( datetime, \"%s\" ) VALUES ( '%s', '%s')",
LDB_TBL_SCHEMA, LDB_TBL_NAME_DATA ,adrStr, dtStr, dpc->dp[i]->value);
res = SQLexec(st);
PQclear(res);
success += 1;
}else{
snprintf(st, CMDBUFFER_SIZE, "UPDATE %s.%s SET \"%s\"='%s' WHERE datetime='%s'",
LDB_TBL_SCHEMA, LDB_TBL_NAME_DATA ,adrStr, dpc->dp[i]->value, dtStr );
res = SQLexec(st);
PQclear(res);
success += 1;
}
}
snprintf(log, LOG_BUF_LEN, "%i values successfully saved", success);
lielas_log((unsigned char*) log, LOG_LEVEL_DEBUG);
return 0;
}
/********************************************************************************************************************************
* int getDeviceData(Ldevice *d): get device data and save it to database
********************************************************************************************************************************/
int getDeviceData(Ldevice *d, datapaketcontainer *dpc){
int i;
time_t rawtime;
struct tm *now;
struct tm timeout;
struct tm dt;
unsigned char buf[DATABUFFER_SIZE] = { 0 };
char datetimestr[CMDBUFFER_SIZE];
char st[CMDBUFFER_SIZE];
char cmd[CMDBUFFER_SIZE];
char adrStr[CMDBUFFER_SIZE];
char log[LOG_BUF_LEN];
coap_buf *cb;
int cnr;
datapaket *dp;
PGresult *res;
int pos = 0;
int eof = 0;
int datasets = 0;
dpc->datapakets = 0;
dpc->dec = 0;
dpc->d = d;
lielas_log((unsigned char*) "get device data", LOG_LEVEL_DEBUG);
cb = coap_create_buf();
if( cb == NULL){
printf("Error: cannot create coap_buf\n");
return -1;
}
cb->buf = (char*)buf;
cb->bufSize = DATABUFFER_SIZE;
time(&rawtime);
now = gmtime(&rawtime);
memcpy(&timeout, now, sizeof(struct tm));
timeout.tm_sec += MAX_DATA_SEND_TIME;
mktime(&timeout);
// get last value in database and create coap get command
snprintf(adrStr, DATABUFFER_SIZE, "%s.1.1", d->mac);
if(SQLTableExists(LDB_TBL_NAME_DATA) == 0){
//Table exists, check if column exists
if(SQLColumnExists(LDB_TBL_NAME_DATA, adrStr)){ //column does not exist, get all data
snprintf(cmd, DATABUFFER_SIZE, "coap://[%s]:5683/database?datetime=%s", d->address, GET_FIRST_VALUE_DATE);
coap_send_cmd(cmd, cb, MYCOAP_METHOD_GET, NULL);
}else{ // column exists, get new data
snprintf(st, DATABUFFER_SIZE,
"SELECT datetime \"%s\" FROM %s.%s WHERE \"%s\" NOT LIKE '' ORDER BY datetime DESC LIMIT 1",
adrStr, LDB_TBL_SCHEMA, LDB_TBL_NAME_DATA , adrStr);
res = SQLexec(st);
if(PQntuples(res) == 1){
strcpy(datetimestr, PQgetvalue(res, 0, 0));
for(i = 0; i < strlen(datetimestr); i++){
if(datetimestr[i] == '-'){
datetimestr[i] = '.';
}
}
for(i = 0; i < strlen(datetimestr); i++){
if(datetimestr[i] == ' '){
datetimestr[i] = '-';
}
}
snprintf(cmd, DATABUFFER_SIZE, "coap://[%s]:5683/database?datetime=%s", d->address, datetimestr);
coap_send_cmd(cmd, cb, MYCOAP_METHOD_GET, NULL);
snprintf(log, LOG_BUF_LEN, "sended get data cmd: %s", cmd);
lielas_log((unsigned char*)log, LOG_LEVEL_DEBUG);
}else{
snprintf(cmd, DATABUFFER_SIZE, "coap://[%s]:5683/database?datetime=%s", d->address, GET_FIRST_VALUE_DATE);
coap_send_cmd(cmd, cb, MYCOAP_METHOD_GET, NULL);
snprintf(log, LOG_BUF_LEN, "sended get data cmd: %s", cmd);
lielas_log((unsigned char*)log, LOG_LEVEL_DEBUG);
}
PQclear(res);
}
}else{
lielas_createDataTbl();
if(SQLTableExists(LDB_TBL_NAME_DATA)){
return -1;
}
snprintf(cmd, DATABUFFER_SIZE, "coap://[%s]:5683/database?datetime=%s", d->address, GET_FIRST_VALUE_DATE);
coap_send_cmd(cmd, cb, MYCOAP_METHOD_GET, NULL);
snprintf(log, LOG_BUF_LEN, "sended get data cmd: %s", cmd);
lielas_log((unsigned char*)log, LOG_LEVEL_DEBUG);
}
if(cb->status != COAP_STATUS_CONTENT){
lielas_log((unsigned char*) "Status error getting data", LOG_LEVEL_DEBUG);
return -1;
}
//parse data
while(pos < cb->len && !eof){
//parse date
lwp_compdt_to_struct_tm((unsigned char*)&cb->buf[pos], &dt);
mktime(&dt);
snprintf(log, LOG_BUF_LEN, "found datetime: %x:%x:%x:%x ... ", (unsigned char)cb->buf[pos], (unsigned char)cb->buf[pos+1], (unsigned char)cb->buf[pos+2], (unsigned char)cb->buf[pos+3]);
strftime(&log[strlen(log)], LOG_BUF_LEN, "%d.%m.%Y %H:%M:%S",&dt);
lielas_log((unsigned char*)log, LOG_LEVEL_DEBUG);
pos += 4;
//test date
if(testDatetime(&dt)){
//parse values
for(cnr = 1; cnr <= d->modul[1]->channels; cnr++){
dp =CreateDatapaket();
if(dp == NULL)
break;
dp->d = d;
dp->m = d->modul[1];
dp->c = d->modul[1]->channel[cnr];
convertValueWithExponent(&cb->buf[pos], dp->value, VALUEBUFFER_SIZE, dp->c->exponent);
pos += 2;
memcpy(dp->dt, &dt, sizeof(struct tm));
dpc->dp[dpc->datapakets] = dp;
dpc->datapakets += 1;
snprintf(log, LOG_BUF_LEN, "found value: %x:%x ... %s", (unsigned char)cb->buf[pos-2], (unsigned char)cb->buf[pos-1], dp->value);
lielas_log((unsigned char*)log, LOG_LEVEL_DEBUG);
datasets += 1;
if(datasets >= MAX_VALUES_IN_PAKET)
break;
}
}else{
lielas_log((unsigned char*) "error parsing log data, invalid datetime ", LOG_LEVEL_WARN);
eof = 1;
break;
}
}
snprintf(log, LOG_BUF_LEN, "received %i values", datasets);
lielas_log((unsigned char*)log, LOG_LEVEL_DEBUG);
return 0;
}