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/CreditCardRecord.db"
#define PARKINGDEDUCT_FILE                 		"/Storage/ChargeLog/ParkingCreditCardRecord.db"
#define POWER_FILE                 			    "/Storage/ChargeLog/PowerConsumption.db"
//------------------------------------------------------------------------------
static sqlite3 *localDb;
static sqlite3 *deductDb;
static sqlite3 *parkingDb;
static sqlite3* powerDb;
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);";

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

    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_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;
}
void CreditCardTimeSet(char *_time, RecordTransactionInfo* deductInfo)
{
    _time[0] = '2';
    _time[1] = '0';
    _time[2] = (char)deductInfo->pCreditCard.TransDate[0];
    _time[3] = (char)deductInfo->pCreditCard.TransDate[1];
    _time[4] = '-';
    _time[5] = (char)deductInfo->pCreditCard.TransDate[2];
    _time[6] = (char)deductInfo->pCreditCard.TransDate[3];
    _time[7] = '-';
    _time[8] = (char)deductInfo->pCreditCard.TransDate[4];
    _time[9] = (char)deductInfo->pCreditCard.TransDate[5];
    _time[10] = ' ';
    _time[11] = (char)deductInfo->pCreditCard.TransTime[0];
    _time[12] = (char)deductInfo->pCreditCard.TransTime[1];
    _time[13] = ':';
    _time[14] = (char)deductInfo->pCreditCard.TransTime[2];
    _time[15] = (char)deductInfo->pCreditCard.TransTime[3];
    _time[16] = ':';
    _time[17] = (char)deductInfo->pCreditCard.TransTime[4];
    _time[18] = (char)deductInfo->pCreditCard.TransTime[5];
    _time[19] = '\0';
}

int DeductDB_Open(void)
{
	int result = PASS;
	char* errMsg = NULL;
	char* createDeductInfoSql="CREATE TABLE IF NOT EXISTS report_credit_deduct("
					      	  "idx integer primary key AUTOINCREMENT, "
							  "gun_index text, "
                              "reportDate text, "
                              "vemData text, "
                              "txId text, "
                              "creditNo text, "
                              "amount text, "
                              "storeId text, "
                              "approvalNo text, "
                              "RRN text, "
                              "ROC text, "
                              "isDonateInvoice text, "
                              "isDeductResult text, "
                              "isIntoCharge text, "
                              "isUpload text, "
                              "lineSn text, "
                              "Energy text, "
                              "RedeductTime "
						  	  ");";

	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];
    char storeId[18];
    char RRN[12];
    char ROC[12];
    char lineSn[20];
    char _time[20];

    memset(approNo, '\0', sizeof(approNo));
    memset(carNo, '\0', sizeof(carNo));
    memset(vemData, '\0', sizeof(vemData));
    memset(storeId, '\0', sizeof(storeId));
    memset(RRN, '\0', sizeof(RRN));
    memset(ROC, '\0', sizeof(ROC));
    memset(lineSn, '\0', sizeof(lineSn));

    strncpy(approNo, (char *)&deductInfo->pCreditCard.ApprovalNo,9);
    strncpy(carNo, (char *)&deductInfo->pCreditCard.CardNo,20);
    strncpy(vemData, (char *)&deductInfo->pCreditCard.VemData,64);
    strncpy(storeId, (char *)&deductInfo->pCreditCard.StoreId,18 );
    strncpy(RRN,(char *)&deductInfo->pCreditCard.RRN,12);
    strncpy(ROC,(char *)&deductInfo->pCreditCard.ROC,12);
    CreditCardTimeSet(&_time[0], deductInfo);
    //sprintf(reportDate, "%s%s", deductInfo->pCreditCard.TransDate, deductInfo->pCreditCard.TransTime);

	sprintf(sqlStr, "insert into report_credit_deduct(gun_index, reportDate, vemData, txId, creditNo, amount, storeId, approvalNo, RRN, ROC, isDonateInvoice, isDeductResult, isIntoCharge, isUpload, lineSn, Energy, RedeductTime) "
				    "values('%d', '%s', '%s', '%d', '%s', '%.1f', '%s', '%s', '%s', '%s', '%d', '%d', '%d' , '%d', '%d', '%.4f', '%d');",
                    gunIndex,
                    _time,
				    vemData,
                    deductInfo->TransactionId,
                    carNo,
                    deductInfo->Amount,
					storeId,
                    approNo,
				    RRN,
				    ROC,
                    (int)deductInfo->IsDonateInvoice,
                    (int)deductInfo->DeductResult,
                    (int)deductInfo->isIntoCharge,
                    (int)deductInfo->IsUpload,
                    deductInfo->LineStatus,
                    deductInfo->Energy,
                    deductInfo->RedeductTime);

    //log_info("%s",sqlStr);

    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 report_credit_deduct where idx < (select idx from report_credit_deduct 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};
    char ApprovalNo[10] = {0};
    memset(ApprovalNo, '\0', 10);
    strncpy(ApprovalNo, (char*)&deductInfo->pCreditCard.ApprovalNo,9);

    sprintf(sqlStr, "update report_credit_deduct set isUpload = %d , isIntoCharge = %d, amount = %.1f, txId = %d, isDeductResult = %d, Energy = %.4f, lineSn = %d, RedeductTime = %d where approvalNo = '%s'; ",
                    (int)deductInfo->IsUpload,
                    (int)deductInfo->isIntoCharge,
                    deductInfo->Amount,
                    deductInfo->TransactionId,
                    (int)deductInfo->DeductResult,
                    deductInfo->Energy,
                    deductInfo->LineStatus,
                    deductInfo->RedeductTime,
                    ApprovalNo);

    //log_info("%s",sqlStr);

    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 UpdateRedeuctBill(int Txid,float amount)
{
    int result = PASS;
    char* errMsg = NULL;
    char sqlStr[1024] = { 0 };

    sprintf(sqlStr, "update report_credit_deduct set amount = %.1f where txId = '%d'; ",
            amount,
            Txid);

    //log_info("%s",sqlStr);

    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 report_credit_deduct where isDeductResult = %d and isUpload = %d;", deductResult > 0 ? 1 : 0, uploadState > 0 ? 1 : 0);
    }
    else if(deductResult >= 0 && uploadState < 0)
    {
        sprintf(sqlStr, "select * from report_credit_deduct where isDeductResult = 0 OR isDeductResult = 3;");
    }
    else if(deductResult < 0 && uploadState >= 0)
    {
        sprintf(sqlStr, "select * from report_credit_deduct where isUpload = %d;", uploadState > 0 ? 1 : 0);
    }
    else
    {
        sprintf(sqlStr, "select * from report_credit_deduct;");
    }

    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->pCreditCard.ApprovalNo, rs[(idxRow * cols) + 5]);
                strcpy((char *)&deductInfo->pCreditCard.CardNo, rs[(idxRow * cols) + 6]);
                strcpy((char *)&deductInfo->pCreditCard.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;
}
void GetDeductTime(char* _time,RecordTransactionInfo* deductInfo)
{
    deductInfo->pCreditCard.TransDate[0] = (unsigned char)_time[2];
    deductInfo->pCreditCard.TransDate[1] = (unsigned char)_time[3];
    deductInfo->pCreditCard.TransDate[2] = (unsigned char)_time[5];
    deductInfo->pCreditCard.TransDate[3] = (unsigned char)_time[6];
    deductInfo->pCreditCard.TransDate[4] = (unsigned char)_time[8];
    deductInfo->pCreditCard.TransDate[5] = (unsigned char)_time[9];

    deductInfo->pCreditCard.TransTime[0] = (unsigned char)_time[11];
    deductInfo->pCreditCard.TransTime[1] = (unsigned char)_time[12];
    deductInfo->pCreditCard.TransTime[2] = (unsigned char)_time[14];
    deductInfo->pCreditCard.TransTime[3] = (unsigned char)_time[15];
    deductInfo->pCreditCard.TransTime[4] = (unsigned char)_time[17];
    deductInfo->pCreditCard.TransTime[5] = (unsigned char)_time[18];

}
// 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 report_credit_deduct where isDeductResult = %d and isUpload = %d;", deductResult > 0 ? 1 : 0, uploadState > 0 ? 1 : 0);
    }
    else if(deductResult >= 0 && uploadState < 0)
    {
        sprintf(sqlStr, "select * from report_credit_deduct where isDeductResult IN (0,3) AND RedeductTime < 10;");
    }
    else if(deductResult < 0 && uploadState >= 0)
    {
        sprintf(sqlStr, "select * from report_credit_deduct where isUpload = %d;", uploadState > 0 ? 1 : 0);
    }
    else
    {
        sprintf(sqlStr, "select * from report_credit_deduct;");
    }

    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].ConnectorID = atoi(rs[(idxRow * cols) + 1]);
               // GetDeductTime(rs[(idxRow * cols) + 1], deductInfo);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.VemData, rs[(idxRow * cols) + 3],64);
                deductInfo[idxRow - 1].TransactionId = atoi(rs[(idxRow * cols) + 4]);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.CardNo, rs[(idxRow * cols) + 5],20);
                deductInfo[idxRow - 1].Amount = atoi(rs[(idxRow * cols) + 6]);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.StoreId, rs[(idxRow * cols) + 7],18);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.ApprovalNo, rs[(idxRow * cols) + 8],9);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.RRN, rs[(idxRow * cols) + 9],12);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.ROC, rs[(idxRow * cols) + 10],12);
                deductInfo[idxRow - 1].IsDonateInvoice = atoi(rs[(idxRow * cols) + 11]);
                deductInfo[idxRow - 1].DeductResult = atoi(rs[(idxRow * cols) + 12]);
                deductInfo[idxRow - 1].isIntoCharge = atoi(rs[(idxRow * cols) + 13]);
                deductInfo[idxRow - 1].IsUpload = atoi(rs[(idxRow * cols) + 14]);
                deductInfo[idxRow - 1].LineStatus = atoi(rs[(idxRow * cols) + 15]);
                deductInfo[idxRow - 1].Energy = atof(rs[(idxRow * cols) + 16]);
                deductInfo[idxRow - 1].RedeductTime = atoi(rs[(idxRow * cols) + 17]);
                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].pCreditCard.VemData);
		}
	}
}

int PowerDB_Open(void)
{
    int result = PASS;
    char* errMsg = NULL;
    char* createPowerConsumption = "CREATE TABLE IF NOT EXISTS power_consumption( "
        "idx INTEGER PRIMARY KEY AUTOINCREMENT, "
        "connector TEXT, "
        "val TEXT);";

    if (sqlite3_open(POWER_FILE, &powerDb))
    {
        result = FAIL;
        log_info("Can't open deduct database: %s", sqlite3_errmsg(powerDb));
        sqlite3_close(powerDb);
    } else
    {
        if (sqlite3_exec(powerDb, 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");
        }

        sqlite3_close(powerDb);
    }

    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(POWER_FILE, &powerDb)) {
        result = FAIL;
        log_info("Can't open database: %s", sqlite3_errmsg(powerDb));
        sqlite3_close(powerDb);
    } 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(powerDb, 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(powerDb);
    }

    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(POWER_FILE, &powerDb)) {
        result = FAIL;
        log_info("Can't open database: %s", sqlite3_errmsg(powerDb));
        sqlite3_close(powerDb);
    } 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(powerDb, 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(powerDb);
    }

    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(POWER_FILE, &powerDb)) {
        result = FAIL;
        log_info("Can't open database: %s", sqlite3_errmsg(powerDb));
        sqlite3_close(powerDb);
    } else {
        //log_info("Local config query database open successfully.");
        sqlite3_get_table(powerDb, 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(powerDb);
    }

    return result;
}

int ParkingDB_Open(void)
{
	int result = PASS;
	char* errMsg = NULL;
	char* createDeductInfoSql="CREATE TABLE IF NOT EXISTS report_Parking_deduct("
					      	  "idx integer primary key AUTOINCREMENT, "
							  "gun_index text, "
                              "vemData text, "
                              "OccupancySN text, "
                              "duration text, "
                              "creditNo text, "
                              "amount text, "
                              "storeId text, "
                              "approvalNo text, "
                              "RRN text, "
                              "ROC text, "
                              "isDonateInvoice text, "
                              "isDeductResult text, "
                              "isUpload text, "
                              "lineSn text, "
                              "StartTime text, "
                              "RedeductTime "
						  	  ");";
	if(sqlite3_open(PARKINGDEDUCT_FILE, &parkingDb))
	{
		result = FAIL;
		log_info("Can't open Parking deduct database: %s", sqlite3_errmsg(parkingDb));
		sqlite3_close(parkingDb);
	}
	else
	{
		//log_info("Deduct database open successfully.");
		if (sqlite3_exec(parkingDb, createDeductInfoSql, 0, 0, &errMsg) != SQLITE_OK)
		{
			result = FAIL;
			log_info("Create Parking deduct info table error message: %s", errMsg);
		}
		else
		{
			log_info("Opened Parking deduct info table successfully");
		}
       
		sqlite3_close(parkingDb);
	}

	return result;
}

int InsertParkingDeductInfo(int gunIndex, RecordTransactionInfo *deductInfo)
{
    int result = PASS;
    char *errMsg = NULL;
    char sqlStr[1024] = {0};
    char approNo[13];
    char carNo[21];
    char OccupancySN[37];
    char storeId[18];
    char RRN[12];
    char ROC[12];
    char lineSn[20];
    char _time[33];
    char vemData[65];

    memset(approNo, '\0', sizeof(approNo));
    memset(carNo, '\0', sizeof(carNo));
    memset(OccupancySN, '\0', sizeof(OccupancySN));
    memset(storeId, '\0', sizeof(storeId));
    memset(RRN, '\0', sizeof(RRN));
    memset(ROC, '\0', sizeof(ROC));
    memset(lineSn, '\0', sizeof(lineSn));
    memset(_time,'\0',sizeof(_time));
    memset(vemData,'\0',sizeof(vemData));

    strncpy(approNo, (char *)&deductInfo->pCreditCard.ApprovalNo,9);
    strncpy(carNo, (char *)&deductInfo->pCreditCard.CardNo,20);
    strncpy(OccupancySN, (char *)&deductInfo->OccupancySN,36);
    strncpy(storeId, (char *)&deductInfo->pCreditCard.StoreId,18 );
    strncpy(RRN,(char *)&deductInfo->pCreditCard.RRN,12);
    strncpy(ROC,(char *)&deductInfo->pCreditCard.ROC,12);
    strncpy(_time,(char *)&deductInfo->ParkingStartTime,33);
    strncpy(vemData,(char *)&deductInfo->pCreditCard.VemData,64);

    //sprintf(reportDate, "%s%s", deductInfo->pCreditCard.TransDate, deductInfo->pCreditCard.TransTime);

	sprintf(sqlStr, "insert into report_Parking_deduct(gun_index, vemData, OccupancySN, duration, creditNo, amount, storeId, approvalNo, RRN, ROC, isDonateInvoice, isDeductResult, isUpload, lineSn, StartTime, RedeductTime) "
				    "values('%d', '%s', '%s', '%d', '%s', '%.1f', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%d', '%s', '%d');",
                    gunIndex,
                    vemData,
				    OccupancySN,
                    deductInfo->ParkingDuration,
                    carNo,
                    deductInfo->Amount,
					storeId,
                    approNo,
				    RRN,
				    ROC,
                    (int)deductInfo->IsDonateInvoice,
                    (int)deductInfo->DeductResult,
                    (int)deductInfo->IsUpload,
                    deductInfo->LineStatus,
                    _time,
                    deductInfo->RedeductTime);

    //log_info("%s",sqlStr);

    if (sqlite3_open(PARKINGDEDUCT_FILE, &parkingDb))
    {
        result = FAIL;
        log_info( "Can't open deduct database: %s", sqlite3_errmsg(parkingDb));
        sqlite3_close(parkingDb);
    }
    else
    {
        //log_info( "Local event record database open successfully.");
        if (sqlite3_exec(parkingDb, 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 report_Parking_deduct where idx < (select idx from report_Parking_deduct order by idx desc limit 1)-10000;");
        if (sqlite3_exec(parkingDb, 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(parkingDb);
    }

    return result;
}

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

    sprintf(sqlStr, "select * from report_Parking_deduct where isUpload = 0;");

    if (sqlite3_open(PARKINGDEDUCT_FILE, &parkingDb))
    {
        log_info("Can't open parking database: %s", sqlite3_errmsg(parkingDb));
        sqlite3_close(parkingDb);
    }
    else
    {
        sqlite3_get_table(parkingDb, 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].ConnectorID = atoi(rs[(idxRow * cols) + 1]);
                // GetDeductTime(rs[(idxRow * cols) + 1], deductInfo);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.VemData, rs[(idxRow * cols) + 2],64);
                memcpy((char*)&deductInfo[idxRow - 1].OccupancySN, rs[(idxRow * cols) + 3],36);
                deductInfo[idxRow - 1].ParkingDuration = atoi(rs[(idxRow * cols) + 4]);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.CardNo, rs[(idxRow * cols) + 5],20);
                deductInfo[idxRow - 1].Amount = atoi(rs[(idxRow * cols) + 6]);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.StoreId, rs[(idxRow * cols) + 7],18);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.ApprovalNo, rs[(idxRow * cols) + 8],9);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.RRN, rs[(idxRow * cols) + 9],12);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.ROC, rs[(idxRow * cols) + 10],12);
                deductInfo[idxRow - 1].IsDonateInvoice = atoi(rs[(idxRow * cols) + 11]);
                deductInfo[idxRow - 1].DeductResult = atoi(rs[(idxRow * cols) + 12]);
                deductInfo[idxRow - 1].IsUpload = atoi(rs[(idxRow * cols) + 13]);
                deductInfo[idxRow - 1].LineStatus = atoi(rs[(idxRow * cols) + 14]);
                memcpy((char*)&deductInfo[idxRow - 1].ParkingStartTime, rs[(idxRow * cols) + 15],32);
                deductInfo[idxRow - 1].RedeductTime = atoi(rs[(idxRow * cols) + 16]);
                if(idxRow >= 128)
                {
                	rows = 128;
                	break;
                }
            }
        }

        sqlite3_free_table(rs);
        sqlite3_close(parkingDb);
    }

    return rows;
}

int DB_GetParkingDeductResult(int *gunIndex, RecordTransactionInfo *deductInfo)
{
    char *errMsg = NULL;
    char sqlStr[1024];
    char **rs;
    int rows = 0, cols = 0;

    sprintf(sqlStr, "select * from report_Parking_deduct where isDeductResult = 0 OR isDeductResult = 3;");

    if (sqlite3_open(PARKINGDEDUCT_FILE, &parkingDb))
    {
        log_info("Can't open parking database: %s", sqlite3_errmsg(parkingDb));
        sqlite3_close(parkingDb);
    }
    else
    {
        sqlite3_get_table(parkingDb, 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].ConnectorID = atoi(rs[(idxRow * cols) + 1]);
                // GetDeductTime(rs[(idxRow * cols) + 1], deductInfo);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.VemData, rs[(idxRow * cols) + 2],64);
                memcpy((char*)&deductInfo[idxRow - 1].OccupancySN, rs[(idxRow * cols) + 3],36);
                deductInfo[idxRow - 1].ParkingDuration = atoi(rs[(idxRow * cols) + 4]);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.CardNo, rs[(idxRow * cols) + 5],20);
                deductInfo[idxRow - 1].Amount = atoi(rs[(idxRow * cols) + 6]);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.StoreId, rs[(idxRow * cols) + 7],18);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.ApprovalNo, rs[(idxRow * cols) + 8],9);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.RRN, rs[(idxRow * cols) + 9],12);
                memcpy((char*)&deductInfo[idxRow - 1].pCreditCard.ROC, rs[(idxRow * cols) + 10],12);
                deductInfo[idxRow - 1].IsDonateInvoice = atoi(rs[(idxRow * cols) + 11]);
                deductInfo[idxRow - 1].DeductResult = atoi(rs[(idxRow * cols) + 12]);
                deductInfo[idxRow - 1].IsUpload = atoi(rs[(idxRow * cols) + 13]);
                deductInfo[idxRow - 1].LineStatus = atoi(rs[(idxRow * cols) + 14]);
                memcpy((char*)&deductInfo[idxRow - 1].ParkingStartTime, rs[(idxRow * cols) + 15],10);
                deductInfo[idxRow - 1].RedeductTime = atoi(rs[(idxRow * cols) + 16]);
                if(idxRow >= 128)
                {
                	rows = 128;
                	break;
                }
            }
        }

        sqlite3_free_table(rs);
        sqlite3_close(parkingDb);
    }

    return rows;
}


int UpdateParkingUpload(char* OccupancySN,int is_upload)
{
    int result = PASS;
    char* errMsg = NULL;
    char sqlStr[1024] = { 0 };

    if (strcmp((char*)OccupancySN,"") == EQUAL) {
        log_info("OccupancySN is NULL");
        return;
    }
    char SN[37];
    memset(SN,'\0',sizeof(SN));
    memcpy((char*)SN,OccupancySN,36);
    
    sprintf(sqlStr, "update report_Parking_deduct set IsUpload = %d where OccupancySN = '%s'; ",
            is_upload,
            SN);

    //log_info("%s",sqlStr);

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

        sqlite3_close(parkingDb);
    }

    return result;
}

int UpdateParkingDeductInfo(int gunIndex, RecordTransactionInfo *deductInfo)
{

    int result = PASS;
    char *errMsg = NULL;
    char sqlStr[1024] = {0};
    char OccupancySN[37] = {0};
    memset(OccupancySN, '\0', 37);
    strncpy(OccupancySN, (char*)&deductInfo->OccupancySN,36);
    if (strcmp((char*)deductInfo->OccupancySN,"") == EQUAL) {
        log_info("OccupancySN is NULL");
        return;
    }

    sprintf(sqlStr, "update report_Parking_deduct set isUpload = %d ,amount = %.1f, isDeductResult = %d, RedeductTime = %d where OccupancySN = '%s'; ",
                    (int)deductInfo->IsUpload,
                    deductInfo->Amount,
                    (int)deductInfo->DeductResult,
                    deductInfo->RedeductTime,
                    deductInfo->OccupancySN);

    //  log_info("%s",sqlStr);

    if (sqlite3_open(PARKINGDEDUCT_FILE, &parkingDb))
    {
        result = FAIL;
        log_info( "Can't open deduct database: %s", sqlite3_errmsg(parkingDb));
        sqlite3_close(parkingDb);
    }
    else
    {
        //log_info( "Local event record database open successfully.");
        if (sqlite3_exec(parkingDb, 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(parkingDb);
    }

    return result;
}