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

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

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

//------------------------------------------------------------------------------
#define DB_FILE                                 "/Storage/ChargeLog/localCgargingRecord.db"

//------------------------------------------------------------------------------
static sqlite3 *localDb;
static DcCommonInfo* ShmDcCommonData = NULL;
//------------------------------------------------------------------------------
//===============================================
// SQLite3 related routine
//===============================================
int DB_Open(void)
{
    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);";

    char *createPowerConsumption = "CREATE TABLE IF NOT EXISTS power_consumption( "
                         "idx INTEGER PRIMARY KEY AUTOINCREMENT, "
                         "connector TEXT, "
                         "val TEXT);";

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

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

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

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

        //DS60-120
        if (sqlite3_exec(localDb, createPowerConsumption, 0, 0, &errMsg) != SQLITE_OK) {
            result = FAIL;
            log_info("Create power consumption record table error message: %s", errMsg);
        } else {
            log_info("Opened power consumption record table successfully");
        }

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

        sqlite3_close(localDb);
    }

    ShmDcCommonData = (DcCommonInfo*)GetShmDcCommonData();
    return result;
}

int DB_Insert_Record(int gunIndex)
{
    int result = PASS;
    char *errMsg = NULL;
    char insertSql[1024];
    struct ChargingInfoData *pDcChargingInfo = (struct ChargingInfoData *)GetDcChargingInfoData(gunIndex);
    struct OCPP16Data *ShmOCPP16Data = (struct OCPP16Data *)GetShmOCPP16Data();

    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');",
            pDcChargingInfo->ReservationId, //DS60-120 add
            ShmOCPP16Data->StartTransaction[gunIndex].ResponseTransactionId,
            pDcChargingInfo->StartMethod,
            pDcChargingInfo->StartUserId,
            pDcChargingInfo->StartDateTime,
            pDcChargingInfo->StopDateTime,
            pDcChargingInfo->EvBatteryStartSoc,
            pDcChargingInfo->EvBatterySoc,
            pDcChargingInfo->PresentChargedEnergy,
            ShmOCPP16Data->StopTransaction[gunIndex].StopReason);

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

        //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(localDb, insertSql, 0, 0, &errMsg) != SQLITE_OK) {
            result = FAIL;
            log_info( "delete local charging error message: %s", errMsg);
        } else {
            //log_info( "delete local charging record successfully");
        }

        sqlite3_close(localDb);
    }

    return result;
}

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

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

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

        sqlite3_close(localDb);
    }

    return result;
}

int DB_Get_Operactive(uint8_t gunIndex)
{
    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;", gunIndex);
    sprintf(sqlStr, "select * from config where item='IsAvailable' and connector=%d;", gunIndex); //DS60-120 add
    //DEBUG_INFO("sqlStr= %s", sqlStr);

    if (sqlite3_open(DB_FILE, &localDb)) {
        result = FAIL;
        log_info( "Can't open database: %s", sqlite3_errmsg(localDb));
        sqlite3_close(localDb);
    } else {
        log_info( "Local config query database open successfully.");
        sqlite3_get_table(localDb, 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", gunIndex, rs[(idxRow * cols) + 3]);
            }
        } else {
            log_info("Query connector-%d fail, set default value to operactive.", gunIndex);
        }

        sqlite3_free_table(rs);
        sqlite3_close(localDb);
    }

    return result;
}

int DB_Insert_PowerConsumption(uint8_t gunIndex, float energy)
{
    uint8_t result = false;
    char* errMsg = NULL;
    char sqlStr[1024];
    srand(time(NULL));

    if (sqlite3_open(DB_FILE, &localDb)) {
        result = FAIL;
        log_info("Can't open database: %s", sqlite3_errmsg(localDb));
        sqlite3_close(localDb);
    } else {
        //log_info("Local charging record database open successfully (%f).", energy);
        sprintf(sqlStr,"insert into power_consumption (connector, val) values (%d, %f);",
            gunIndex,
            energy); //DS60-120 add
        //log_info("sqlStr= %s", sqlStr);
        if (sqlite3_exec(localDb, sqlStr, 0, 0, &errMsg) != SQLITE_OK) {
            result = FAIL;
            log_info("Insert power consumption error message: %s", errMsg);
        } else {
            log_info("Insert connector-%d config item PowerComsumption to %f", gunIndex, energy);
        }

        sqlite3_close(localDb);
    }

    return result;
}

int DB_Update_PowerConsumption(uint8_t gunIndex, float energy)
{
    uint8_t result = false;
    char* errMsg = NULL;
    char sqlStr[1024];
    srand(time(NULL));

    if (sqlite3_open(DB_FILE, &localDb)) {
        result = FAIL;
        log_info("Can't open database: %s", sqlite3_errmsg(localDb));
        sqlite3_close(localDb);
    } else {
        //log_info("Local charging record database open successfully.");
        sprintf(sqlStr,"update power_consumption set val = %f where connector = %d; ",
                        energy,
                        gunIndex); //DS60-120 add
        //log_info("sqlStr= %s", sqlStr);
        if (sqlite3_exec(localDb, sqlStr, 0, 0, &errMsg) != SQLITE_OK) {
            result = FAIL;
            log_info("update config error message: %s", errMsg);
        } else {
            //log_info("update connector-%d config item PowerComsumption to %f", gunIndex, energy);
        }

        sqlite3_close(localDb);
    }

    return result;
}

int DB_Get_PowerConsumption(uint8_t gunIndex)
{
    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;", gunIndex);
    sprintf(sqlStr, "select * from power_consumption where connector=%d;", gunIndex); //DS60-120 add
    //DEBUG_INFO("sqlStr= %s", sqlStr);

    if (sqlite3_open(DB_FILE, &localDb)) {
        result = FAIL;
        log_info("Can't open database: %s", sqlite3_errmsg(localDb));
        sqlite3_close(localDb);
    } else {
        log_info("Local config query database open successfully.");
        sqlite3_get_table(localDb, sqlStr, &rs, &rows, &cols, &errMsg);
        if (rows > 0) {
            for (int idxRow = 1; idxRow <= rows; idxRow++) {
                ShmDcCommonData->pGunInfo[gunIndex].PowerConsumption = atof(rs[(idxRow * cols) + 2]);
                log_info("Query connector-%d PowerConsumption: %s", gunIndex, rs[(idxRow * cols) + 2]);
                
            }
        } else {
            log_info("Query connector-%d fail, set default value to operactive.", gunIndex);
            result = false;
        }

        sqlite3_free_table(rs);
        sqlite3_close(localDb);
    }

    return result;
}


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

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

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

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

        sqlite3_close(localDb);
    }

    return result;
}

//------------------------------------------------------------------------------
//for Module_EventLog
//------------------------------------------------------------------------------
int CreateEventRecord(void)
{
    int result = PASS;
    char *errMsg = NULL;
    char *createRecordSql = "CREATE TABLE IF NOT EXISTS event_record("
                            "idx integer primary key AUTOINCREMENT, "
                            "occurDatetime text NOT NULL, "
                            "statusCode text NOT NULL"
                            ");";

    if (sqlite3_open(DB_FILE, &localDb)) {
        result = FAIL;
        log_error( "Can't open database: %s", sqlite3_errmsg(localDb));
        sqlite3_close(localDb);
    } else {
        log_info( "Local event record database open successfully.");

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

        sqlite3_close(localDb);
    }

    return result;
}

int InsertEventRecord(uint8_t *statusCode)
{
    int result = PASS;
    char *errMsg = NULL;
    char sqlStr[1024] = {0};

    sprintf(sqlStr, "insert into event_record(occurDatetime, statusCode) values(CURRENT_TIMESTAMP, '%s');", statusCode);

    if (sqlite3_open(DB_FILE, &localDb)) {
        result = FAIL;
        log_info( "Can't open database: %s", sqlite3_errmsg(localDb));
        sqlite3_close(localDb);
    } else {
        //log_info( "Local event record database open successfully.");
        if (sqlite3_exec(localDb, sqlStr, 0, 0, &errMsg) != SQLITE_OK) {
            result = FAIL;
            log_info( "Insert local event record error message: %s", errMsg);
        } else {
            //log_info( "Insert local event record successfully");
        }

        sprintf(sqlStr, "delete from event_record where idx < (select idx from event_record order by idx desc limit 1)-2000;");
        if (sqlite3_exec(localDb, sqlStr, 0, 0, &errMsg) != SQLITE_OK) {
            result = FAIL;
            log_info( "delete local event_record error message: %s", errMsg);
        } else {
            //log_info( "delete local event record successfully");
        }

        sqlite3_close(localDb);
    }

    return result;
}