csu3_am335x/EVSE/Projects/DD360Tcci/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"
#define DEDUCT_FILE                 			"/Storage/ChargeLog/localDeductRecord.db"

//------------------------------------------------------------------------------
static sqlite3 *localDb;
static sqlite3 *deductDb;

//------------------------------------------------------------------------------
//===============================================
// 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);";

    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");
        }

        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);
    }

    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_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 DeductDB_Open(void)
{
	int result = PASS;
	char* errMsg = NULL;
	char* createDeductInfoSql="CREATE TABLE IF NOT EXISTS deduct_record("
					      	  "idx integer primary key AUTOINCREMENT, "
							  "gunIndex text, "
						  	  "transactionId text, "
						  	  "deductResult text, "
						  	  "isDonate text, "
						  	  "approvalNo text, "
						  	  "cardNo text,"
						  	  "vemData text, "
						  	  "amount text, "
						  	  "isUpload text"
						  	  ");";

	if(sqlite3_open(DEDUCT_FILE, &deductDb))
	{
		result = FAIL;
		log_info("Can't open deduct database: %s", sqlite3_errmsg(deductDb));
		sqlite3_close(deductDb);
	}
	else
	{
		log_info("Deduct database open successfully.");

		if (sqlite3_exec(deductDb, createDeductInfoSql, 0, 0, &errMsg) != SQLITE_OK)
		{
			result = FAIL;
			log_info("Create deduct info table error message: %s", errMsg);
		}
		else
		{
			log_info("Opened deduct info table successfully");
		}

		sqlite3_close(deductDb);
	}

	return result;
}

int InsertDeductInfo(int gunIndex, RecordTransactionInfo *deductInfo)
{
    int result = PASS;
    char *errMsg = NULL;
    char sqlStr[1024] = {0};
    char approNo[13];
    char carNo[21];
    char vemData[65];

    memset(approNo, 0x00, sizeof(approNo));
    memset(carNo, 0x00, sizeof(carNo));
    memset(vemData, 0x00, sizeof(vemData));

    memcpy(approNo, (char *)&deductInfo->ApprovalNo, sizeof(deductInfo->ApprovalNo));
    memcpy(carNo, (char *)&deductInfo->CardNo, sizeof(deductInfo->CardNo));
    memcpy(vemData, (char *)&deductInfo->VemData, sizeof(deductInfo->VemData));

	sprintf(sqlStr, "insert into deduct_record(gunIndex, transactionId, deductResult, isDonate, approvalNo, cardNo, vemData, amount, isUpload) "
				    "values('%d', '%d', '%d', '%d', '%s', '%s', '%s', '%f', '%d');",
				    gunIndex,
				    deductInfo->TransactionId,
				    deductInfo->DeductResult,
				    deductInfo->IsDonateInvoice,
					approNo,
					carNo,
					vemData,
				    deductInfo->Amount,
				    deductInfo->IsUpload);

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


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

        sqlite3_close(deductDb);
    }

    return result;
}

int UpdateDeductInfoStatus(int gunIndex, RecordTransactionInfo *deductInfo)
{
    int result = PASS;
    char *errMsg = NULL;
    char sqlStr[1024] = {0};

    sprintf(sqlStr, "update deduct_record set deductResult = %d, isUpload = %d where transactionId = %d;",
                    deductInfo->DeductResult,
                    deductInfo->IsUpload,
                    deductInfo->TransactionId);

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

        sqlite3_close(deductDb);
    }

    return result;
}

int DB_GetDeductInfo(int deductResult, int uploadState, int *gunIndex, RecordTransactionInfo *deductInfo)
{
    int result = PASS;
    char *errMsg = NULL;
    char sqlStr[1024];
    char **rs;
    int rows, cols;

    if(deductResult >= 0 && uploadState >= 0)
    {
        sprintf(sqlStr, "select * from deduct_record where deductResult = %d and isUpload = %d;", deductResult > 0 ? 1 : 0, uploadState > 0 ? 1 : 0);
    }
    else if(deductResult >= 0 && uploadState < 0)
    {
        sprintf(sqlStr, "select * from deduct_record where deductResult = %d;", deductResult > 0 ? 1 : 0);
    }
    else if(deductResult < 0 && uploadState >= 0)
    {
        sprintf(sqlStr, "select * from deduct_record where isUpload = %d;", uploadState > 0 ? 1 : 0);
    }
    else
    {
        sprintf(sqlStr, "select * from deduct_record;");
    }

    if (sqlite3_open(DEDUCT_FILE, &deductDb))
    {
        result = FAIL;
        log_info("Can't open deduct database: %s", sqlite3_errmsg(deductDb));
        sqlite3_close(deductDb);
    }
    else
    {
        sqlite3_get_table(deductDb, sqlStr, &rs, &rows, &cols, &errMsg);

        if (rows > 0)
        {
            for (int idxRow = 1; idxRow <= rows; idxRow++)
            {
                *gunIndex = atoi(rs[(idxRow * cols) + 1]);
                deductInfo->TransactionId = atoi(rs[(idxRow * cols) + 2]);
                deductInfo->DeductResult = atoi(rs[(idxRow * cols) + 3]);
                deductInfo->IsDonateInvoice = atoi(rs[(idxRow * cols) + 4]);
                strcpy((char *)&deductInfo->ApprovalNo, rs[(idxRow * cols) + 5]);
                strcpy((char *)&deductInfo->CardNo, rs[(idxRow * cols) + 6]);
                strcpy((char *)&deductInfo->VemData, rs[(idxRow * cols) + 7]);
                deductInfo->Amount = atoi(rs[(idxRow * cols) + 8]);
                deductInfo->IsUpload = atoi(rs[(idxRow * cols) + 9]);
                break;
            }
        }
        else
        {
            result = FAIL;
        }
        sqlite3_free_table(rs);
        sqlite3_close(deductDb);
    }

    return result;
}

int DB_GetReDeductInfo(int *gunIndex, RecordTransactionInfo *deductInfo)
{
    int result = PASS;

    if(DB_GetDeductInfo(0, -1, gunIndex, deductInfo) == PASS)
    {
        //log_info("GetReDeductInfo OK");
    }
    else
    {
        result = FAIL;
        //log_info("GetReDeductInfo NG");
    }

    return result;
}

int DB_GetReUploadDeduct(int *gunIndex, RecordTransactionInfo *deductInfo)
{
    int result = PASS;

    if(DB_GetDeductInfo(-1, 0, gunIndex, deductInfo) == PASS)
    {
        //log_info("GetReUploadDeduct OK");
    }
    else
    {
        result = FAIL;
        //log_info("GetReUploadDeduct NG");
    }

    return result;
}

// return quantity of DeductInfo
int DB_GetMultiDeductInfo(int deductResult, int uploadState, int *gunIndex, RecordTransactionInfo *deductInfo)
{
    char *errMsg = NULL;
    char sqlStr[1024];
    char **rs;
    int rows = 0, cols = 0;

    if(deductResult >= 0 && uploadState >= 0)
    {
        sprintf(sqlStr, "select * from deduct_record where deductResult = %d and isUpload = %d;", deductResult > 0 ? 1 : 0, uploadState > 0 ? 1 : 0);
    }
    else if(deductResult >= 0 && uploadState < 0)
    {
        sprintf(sqlStr, "select * from deduct_record where deductResult = %d;", deductResult > 0 ? 1 : 0);
    }
    else if(deductResult < 0 && uploadState >= 0)
    {
        sprintf(sqlStr, "select * from deduct_record where isUpload = %d;", uploadState > 0 ? 1 : 0);
    }
    else
    {
        sprintf(sqlStr, "select * from deduct_record;");
    }

    if (sqlite3_open(DEDUCT_FILE, &deductDb))
    {
        log_info("Can't open deduct database: %s", sqlite3_errmsg(deductDb));
        sqlite3_close(deductDb);
    }
    else
    {
        sqlite3_get_table(deductDb, sqlStr, &rs, &rows, &cols, &errMsg);

        if (rows > 0)
        {
            for (int idxRow = 1; idxRow <= rows; idxRow++)
            {
                gunIndex[idxRow - 1] = atoi(rs[(idxRow * cols) + 1]);
                deductInfo[idxRow - 1].TransactionId = atoi(rs[(idxRow * cols) + 2]);
                deductInfo[idxRow - 1].DeductResult = atoi(rs[(idxRow * cols) + 3]);
                deductInfo[idxRow - 1].IsDonateInvoice = atoi(rs[(idxRow * cols) + 4]);
                strcpy((char *)&deductInfo[idxRow - 1].ApprovalNo, rs[(idxRow * cols) + 5]);
                strcpy((char *)&deductInfo[idxRow - 1].CardNo, rs[(idxRow * cols) + 6]);
                strcpy((char *)&deductInfo[idxRow - 1].VemData, rs[(idxRow * cols) + 7]);
                deductInfo[idxRow - 1].Amount = atoi(rs[(idxRow * cols) + 8]);
                deductInfo[idxRow - 1].IsUpload = atoi(rs[(idxRow * cols) + 9]);

                if(idxRow >= 128)
                {
                	rows = 128;
                	break;
                }
            }
        }

        sqlite3_free_table(rs);
        sqlite3_close(deductDb);
    }

    return rows;
}

int DB_GetMultiReDeductInfo(int *gunIndex, RecordTransactionInfo *deductInfo)
{
    int quantity = 0;

    quantity = DB_GetMultiDeductInfo(0, -1, gunIndex, deductInfo);

    return quantity;
}

int DB_GetMultiReUploadDeduct(int *gunIndex, RecordTransactionInfo *deductInfo)
{
	int quantity = 0;

	quantity = DB_GetMultiDeductInfo(-1, 0, gunIndex, deductInfo);

    return quantity;
}

void ReDeductTest()
{
	int quantity = 0;
	int gunList[128];
	RecordTransactionInfo deductInfo[128];

	quantity = DB_GetMultiReDeductInfo(gunList, deductInfo);

	if(quantity > 0)
	{
		for(int i = 0; i < quantity; i++)
		{
			printf("Gun %d Rededuct CardNo: %s", gunList[i], deductInfo[i].CardNo);
		}
	}
}