csu3_am335x/EVSE/Projects/DD360Audi/Apps/DataBase/DataBase.c

#include <stdio.h>      /*標準輸入輸出定義*/
#include <stdlib.h>     /*標準函數庫定義*/
#include <string.h>
#include <stdint.h>

#include "../Config.h"
#include "../Log/log.h"
#include "../Define/define.h"
#include "../ShareMemory/shmMem.h"

//------------------------------------------------------------------------------
int DB_Open(sqlite3 *db)
{
    int result = PASS;
    char *errMsg = NULL;
    char *createRecordSql = "CREATE TABLE IF NOT EXISTS charging_record("
                            "idx integer primary key AUTOINCREMENT, "
                            "reservationId text, "
                            "transactionId text, "
                            "startMethod text, "
                            "userId text, "
                            "dateTimeStart text, "
                            "dateTimeStop text,"
                            "socStart text, "
                            "socStop text, "
                            "chargeEnergy text, "
                            "stopReason text"
                            ");";

    /*char *createCfgSql = "CREATE TABLE IF NOT EXISTS `config` ( "
                         "`idx` INTEGER PRIMARY KEY AUTOINCREMENT, "
                         "`IsAvailable` TEXT NOT NULL, "
                         "`connector` INTEGER NOT NULL, "
                         "`val` TEXT NOT NULL, unique(IsAvailable,connector) on conflict replace);";
    */
    //DS60-120 add
    char *createCfgSql = "CREATE TABLE IF NOT EXISTS `config` ( "
                         "`idx` INTEGER PRIMARY KEY AUTOINCREMENT, "
                         "`item` TEXT NOT NULL, "
                         "`connector` INTEGER NOT NULL, "
                         "`val` TEXT NOT NULL, unique(item,connector) on conflict replace);";

    char *createrecordSql = "CREATE TABLE IF NOT EXISTS `event_record` ( "
                            "`idx` INTEGER PRIMARY KEY AUTOINCREMENT, "
                            "`occurDatetime` TEXT NOT NULL, "
                            "`statusCode` TEXT NOT NULL, unique(occurDatetime,statusCode) on conflict replace);";

    char *createrebootSql = "CREATE TABLE IF NOT EXISTS `reboot_record` ( "
                            "`idx` INTEGER PRIMARY KEY AUTOINCREMENT, "
                            "`rebootDatetime` TEXT NOT NULL, unique(rebootDatetime) on conflict replace);";

    if (sqlite3_open(DB_FILE, &db)) {
        result = FAIL;
        log_info( "Can't open database: %s\r\n", sqlite3_errmsg(db));
        sqlite3_close(db);
    } else {
        log_info( "Local charging record database open successfully.\r\n");

        if (sqlite3_exec(db, createRecordSql, 0, 0, &errMsg) != SQLITE_OK) {
            result = FAIL;
            log_info( "Create local charging record table error message: %s\n", errMsg);
        } else {
            log_info( "Opened local charging record table successfully\n");
        }

        if (sqlite3_exec(db, createCfgSql, 0, 0, &errMsg) != SQLITE_OK) {
            result = FAIL;
            log_info( "Create local config table error message: %s\n", errMsg);
        } else {
            log_info( "Opened local config table successfully\n");
        }

        //DS60-120
        if (sqlite3_exec(db, createrecordSql, 0, 0, &errMsg) != SQLITE_OK) {
            result = FAIL;
            log_info( "Create local record table error message: %s\n", errMsg);
        } else {
            log_info( "Opened local record table successfully\n");
        }

        if (sqlite3_exec(db, createrebootSql, 0, 0, &errMsg) != SQLITE_OK) {
            result = FAIL;
            log_info( "Create reboot record table error message: %s\n", errMsg);
        } else {
            log_info( "Opened reboot record table successfully\n");
        }
        //-----

        sqlite3_close(db);
    }

    return result;
}

int DB_Insert_Record(sqlite3 *db, int gun_index)
{
    int result = PASS;
    char *errMsg = NULL;
    char insertSql[1024];

    sprintf(insertSql, "insert into charging_record(reservationId, transactionId, startMethod, userId, dateTimeStart, dateTimeStop, socStart, socStop, chargeEnergy, stopReason) "
            "values('%d', '%d', '%d', '%s', '%s', '%s', '%d', '%d', '%f', '%s');",
            chargingInfo[gun_index]->ReservationId, //DS60-120 add
            ShmOCPP16Data->StartTransaction[gun_index].ResponseTransactionId,
            chargingInfo[gun_index]->StartMethod,
            chargingInfo[gun_index]->StartUserId,
            chargingInfo[gun_index]->StartDateTime,
            chargingInfo[gun_index]->StopDateTime,
            chargingInfo[gun_index]->EvBatteryStartSoc,
            chargingInfo[gun_index]->EvBatterySoc,
            chargingInfo[gun_index]->PresentChargedEnergy,
            //DS60-120 remove
            //ShmSysConfigAndInfo->SysInfo.AcChargingData[gun_index].ReservationId,
            //ShmOCPP16Data->StartTransaction[gun_index].ResponseTransactionId,
            //ShmSysConfigAndInfo->SysInfo.AcChargingData[gun_index].StartMethod,
            //ShmSysConfigAndInfo->SysInfo.AcChargingData[gun_index].StartUserId,
            //ShmSysConfigAndInfo->SysInfo.AcChargingData[gun_index].StartDateTime,
            //ShmSysConfigAndInfo->SysInfo.AcChargingData[gun_index].StopDateTime,
            //ShmSysConfigAndInfo->SysInfo.AcChargingData[gun_index].EvBatterySoc,
            //ShmSysConfigAndInfo->SysInfo.AcChargingData[gun_index].EvBatterySoc,
            //ShmSysConfigAndInfo->SysInfo.AcChargingData[gun_index].PresentChargedEnergy,
            ShmOCPP16Data->StopTransaction[gun_index].StopReason);

    //if (sqlite3_open("/Storage/ChargeLog/localCgargingRecord.db", &db)) {
    if (sqlite3_open(DB_FILE, &db)) { //DS60-120 add
        result = FAIL;
        log_info( "Can't open database: %s\r\n", sqlite3_errmsg(db));
        sqlite3_close(db);
    } else {
        log_info( "Local charging record database open successfully.\r\n");
        if (sqlite3_exec(db, insertSql, 0, 0, &errMsg) != SQLITE_OK) {
            result = FAIL;
            log_info( "Insert local charging record error message: %s\n", errMsg);
        } else {
            log_info( "Insert local charging record successfully\n");
        }

        //DS60-120 add
        sprintf(insertSql, "delete from charging_record where idx < (select idx from charging_record order by idx desc limit 1)-2000;");
        if (sqlite3_exec(db, insertSql, 0, 0, &errMsg) != SQLITE_OK) {
            result = FAIL;
            log_info( "delete local charging error message: %s\n", errMsg);
        } else {
            log_info( "delete local charging record successfully\n");
        }

        sqlite3_close(db);
    }

    return result;
}

int DB_Update_Operactive(sqlite3 *db, uint8_t gun_index, uint8_t IsAvailable)
{
    uint8_t result = false;
    char *errMsg = NULL;
    char sqlStr[1024];
    srand(time(NULL));

    if (sqlite3_open(DB_FILE, &db)) {
        result = FAIL;
        log_info( "Can't open database: %s\r\n", sqlite3_errmsg(db));
        sqlite3_close(db);
    } else {
        log_info( "Local charging record database open successfully (%d).\r\n", IsAvailable);

        //sprintf(sqlStr, "insert or replace into config (IsAvailable, connector, val) values('IsAvailable', %d, %d);", gun_index, IsAvailable);
        sprintf(sqlStr, "insert or replace into config (item, connector, val) values('IsAvailable', %d, %d);", gun_index, IsAvailable); //DS60-120 add
        log_info("sqlStr= %s\r\n", sqlStr);
        if (sqlite3_exec(db, sqlStr, 0, 0, &errMsg) != SQLITE_OK) {
            result = FAIL;
            log_info( "update config error message: %s\n", errMsg);
        } else {
            log_info("update connector-%d config item isOperactive to %d\r\n", gun_index, IsAvailable);
        }

        sqlite3_close(db);
    }

    return result;
}

int DB_Get_Operactive(sqlite3 *db, uint8_t gun_index)
{
    uint8_t result = true;
    char *errMsg = NULL;
    char sqlStr[1024];
    char **rs;
    int  rows, cols;

    //sprintf(sqlStr, "select * from config where IsAvailable='IsAvailable' and connector=%d;", gun_index);
    sprintf(sqlStr, "select * from config where item='IsAvailable' and connector=%d;", gun_index); //DS60-120 add
    //DEBUG_INFO("sqlStr= %s\r\n", sqlStr);

    if (sqlite3_open(DB_FILE, &db)) {
        result = FAIL;
        log_info( "Can't open database: %s\r\n", sqlite3_errmsg(db));
        sqlite3_close(db);
    } else {
        log_info( "Local config query database open successfully.\r\n");
        sqlite3_get_table(db, sqlStr, &rs, &rows, &cols, &errMsg);
        if (rows > 0) {
            for (int idxRow = 1; idxRow <= rows; idxRow++) {
                if (strcmp(rs[(idxRow * cols) + 3], "0") == 0) {
                    result = false;
                }
                log_info("Query connector-%d isOperactive: %s\r\n", gun_index, rs[(idxRow * cols) + 3]);
            }
        } else {
            log_info("Query connector-%d fail, set default value to operactive.\r\n", gun_index);
        }

        sqlite3_free_table(rs);
        sqlite3_close(db);
    }

    return result;
}

int DB_Reboot_Record(sqlite3 *db)
{
    int result = PASS;
    char *errMsg = NULL;
    char insertSql[256];

    sprintf(insertSql, "insert into reboot_record(rebootDatetime) values(CURRENT_TIMESTAMP);");

    if (sqlite3_open(DB_FILE, &db)) {
        result = FAIL;
        log_info( "Can't open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
    } else {
        log_info( "Local charging record database open successfully.\n");
        if (sqlite3_exec(db, insertSql, 0, 0, &errMsg) != SQLITE_OK) {
            result = FAIL;
            log_info( "Insert reboot record error message: %s\n", errMsg);
        } else {
            log_info( "Insert reboot record successfully\n");
        }

        sprintf(insertSql, "delete from reboot_record where idx < (select idx from charging_record order by idx desc limit 1)-2000;");
        if (sqlite3_exec(db, insertSql, 0, 0, &errMsg) != SQLITE_OK) {
            result = FAIL;
            log_info( "delete reboot record error message: %s\n", errMsg);
        } else {
            log_info( "delete reboot record successfully\n");
        }

        sqlite3_close(db);
    }

    return result;
}