csu3_am335x/EVSE/Projects/DD360ComBox/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"
#include "../SelectGun/SelectGun.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);";

	char* createDcMeterSql="CREATE TABLE IF NOT EXISTS ocmf_record("
							  "idx integer primary key AUTOINCREMENT, "
							  "transactionId text, "
			 	 	 	 	  "status INTEGER NOT NULL, "
							  "connector INTEGER NOT NULL, "
							  "publicKey text, "
							  "ocmfInfo 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");
        }
        //-----

		if (sqlite3_exec(localDb, createDcMeterSql, 0, 0, &errMsg) != SQLITE_OK)
		{
			result = FAIL;
			log_info( "Create OCMF record table error message: %s\n", errMsg);
		}
		else
		{
			log_info( "Opened OCMF record table successfully\n");
		}
        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();
    SelectGunInfo *ShmSelectGunInfo = (SelectGunInfo *)GetShmSelectGunInfo();

    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
            ShmSelectGunInfo->PricesInfo[gunIndex].TransactionId,
            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;
}


int DB_Insert_ocmf(uint8_t gun_index)
{
	int result = PASS;
	char* errMsg = NULL;
	char insertSql[2048];
    SelectGunInfo *ShmSelectGunInfo = (SelectGunInfo *)GetShmSelectGunInfo();
	memset(insertSql, 0, sizeof(insertSql));
	sprintf(insertSql, "insert into ocmf_record (transactionId, status, connector) values('%d', 0, '%d');",
					ShmSelectGunInfo->PricesInfo[gun_index].TransactionId,
					gun_index);

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

		sqlite3_close(localDb);
	}

	return result;
}

void DB_Update_ocmf(uint8_t gun_index)
{
	char* errMsg = NULL;
	char insertSql[4096];
    struct SysInfoData *pSysInfo = (struct SysInfoData *)GetShmSysInfoData();
    SelectGunInfo *ShmSelectGunInfo = (SelectGunInfo *)GetShmSelectGunInfo();
	memset(insertSql, 0, sizeof(insertSql));
	sprintf(insertSql, "update ocmf_record set status=0, publicKey='%s', ocmfInfo='%s' where status=0 and transactionId=%d and connector=%d;",
		pSysInfo->DcMeterStatusInfo[gun_index].publicKeyOcmf,
		pSysInfo->DcMeterTransactionAction[gun_index].transactionOCMF,
		ShmSelectGunInfo->PricesInfo[gun_index].TransactionId,
		gun_index);
	if (sqlite3_open(DB_FILE, &localDb))
	{
		log_warn("Can't open database: %s\n", sqlite3_errmsg(localDb));
		sqlite3_close(localDb);
	}
	else
	{
		if (sqlite3_exec(localDb, insertSql, 0, 0, & errMsg) != SQLITE_OK)
		{
			log_warn("Update ocmf error message : %s", errMsg);
		}
		else
		{
			log_info("Update ocmf successfully ");
		}
		sqlite3_close(localDb);
	}
}

void DB_Update_ocmf_Record(uint8_t gun_index, int _trid, bool isGet)
{
	char* errMsg = NULL;
	char insertSql[4096];
    struct SysInfoData *pSysInfo = (struct SysInfoData *)GetShmSysInfoData();
	memset(insertSql, 0, sizeof(insertSql));
	if (isGet)
	{
		sprintf(insertSql, "update ocmf_record set status=0, publicKey='%s', ocmfInfo='%s' where status=0 and transactionId=%d and connector=%d;",
                pSysInfo->DcMeterStatusInfo[gun_index].publicKeyOcmf,
				pSysInfo->DcMeterReadTransactionRecord[gun_index].transactionOCMF,
				_trid,
				gun_index);
	}
	else
	{
		sprintf(insertSql, "update ocmf_record set status=2 where status=0 and transactionId=%d and connector=%d;",
				_trid,
				gun_index);
	}
	if (sqlite3_open(DB_FILE, &localDb))
	{
		log_warn("Can't open database: %s\n", sqlite3_errmsg(localDb));
		sqlite3_close(localDb);
	}
	else
	{
		if (sqlite3_exec(localDb, insertSql, 0, 0, & errMsg) != SQLITE_OK)
		{
			log_warn("Update ocmf error message : %s", errMsg);
		}
		else
		{
			log_info("Update ocmf_Record successfully ");
		}
		sqlite3_close(localDb);
	}
}

int DB_Get_OcmfTrid(uint8_t gun_index)
{
	int result = 0;
	char* errMsg = NULL;
	char sqlStr[2048];
	char **rs;
	int rows, cols;

	memset(sqlStr, 0, sizeof(sqlStr));
	sprintf(sqlStr, "select * from ocmf_record where status=0 and connector=%d and transactionId!=0;", gun_index);

	if (sqlite3_open(DB_FILE, &localDb))
	{
		log_warn("Can't open database: %s\r\n", sqlite3_errmsg(localDb));
		sqlite3_close(localDb);
	}
	else
	{
		//log_info("Local config query database open successfully.\r\n");
		sqlite3_get_table(localDb, sqlStr, &rs, &rows, &cols, &errMsg);
		if (rows > 0)
		{
			for (int idxRow = 1; idxRow <= rows; idxRow++)
			{
				result = atoi(rs[(idxRow * cols) + 1]);
				break;
			}
		}

		sqlite3_free_table(rs);
		sqlite3_close(localDb);
	}

	return result;
}

void DB_Upload_ocmf(uint8_t gun_index, int txid)
{
	char* errMsg = NULL;
	char insertSql[4096];
    struct SysInfoData *pSysInfo = (struct SysInfoData *)GetShmSysInfoData();
    SelectGunInfo *ShmSelectGunInfo = (SelectGunInfo *)GetShmSelectGunInfo();
	memset(insertSql, 0, sizeof(insertSql));
	sprintf(insertSql, "update ocmf_record set status=1 where transactionId=%d and connector=%d;",txid,gun_index);
	if (sqlite3_open(DB_FILE, &localDb))
	{
		log_warn("Can't open database: %s\n", sqlite3_errmsg(localDb));
		sqlite3_close(localDb);
	}
	else
	{
		if (sqlite3_exec(localDb, insertSql, 0, 0, & errMsg) != SQLITE_OK)
		{
			log_warn("Upload ocmf error message : %s", errMsg);
		}
		else
		{
			log_info("Upload ocmf successfully ");
		}
		sqlite3_close(localDb);
	}
}