void __fastcall TFrmAlarmDetailList::ChartAlarmClickSeries(TCustomChart *Sender, TChartSeries *Series,
int ValueIndex, TMouseButton Button, TShiftState Shift, int X,
int Y)
{
String strStartDateTime = FormatDateTime("yyyy-mm-dd 오전 00:00:00", DateTimeAlarmStart->Date);
String strEndDateTime = FormatDateTime("yyyy-mm-dd 오전 00:00:00", DateTimeAlarmEnd->Date + 1);
try
{
AnsiString szQuery = " select *, count(Alarm_idx) AS Frequency from AlarmLog "
" where Set_date BETWEEN '" + strStartDateTime + "' AND '" + strEndDateTime + "'"
" AND Alarm_idx < 1000 group by Alarm_idx order by Frequency DESC;";
CppSQLite3Table OrderedTable = dbMain.getTable(szQuery.c_str());
OrderedTable.setRow(ValueIndex);
AnsiString strCode = (String)OrderedTable.fieldValue("Alarm_idx");
AnsiString szAlarmQuery = " select * from AlarmLog, Alarm, Groups, Recipe where Alarm_idx like " + strCode +
" AND Set_date BETWEEN '" + strStartDateTime + "' AND '" + strEndDateTime + "'"
" AND AlarmLog.Alarm_idx = Alarm.idx"
" AND AlarmLog.Group_idx = Groups.idx"
" AND AlarmLog.group_idx = Recipe.group_idx"
" AND AlarmLog.Recipe_idx = Recipe.idx ORDER BY Set_Date DESC";
CppSQLite3Table SelectedTable = dbMain.getTable(szAlarmQuery.c_str());
for(int i = 1; i < grdAlarmLog->RowCount; i++)
{
grdAlarmLog->Rows[i]->Clear();
}
grdAlarmLog->RowCount = 2;
if (SelectedTable.numRows() > 0)
{
for(int row = 0; row < SelectedTable.numRows(); row++)
{
SelectedTable.setRow(row);
grdAlarmLog->Cells[0][row+1] = IntToStr(row+1);
grdAlarmLog->Cells[1][row+1] = (String)SelectedTable.fieldValue(7); // Start date
grdAlarmLog->Cells[2][row+1] = (String)SelectedTable.fieldValue(8); // End date
grdAlarmLog->Cells[3][row+1] = (String)SelectedTable.fieldValue(9); // Alarm Code
grdAlarmLog->Cells[4][row+1] = (String)SelectedTable.fieldValue(11); // Alarm Name
grdAlarmLog->RowCount = row+2;
}
}
}
catch (CppSQLite3Exception& e)
{
cerr << e.errorCode() << ":" << e.errorMessage() << endl;
}
}
void __fastcall TFrmAlarmDetailList::BtnCodeReadDataClick(TObject *Sender)
{
for(int i = 1; i <=grdAlarmLog->RowCount; i++)
{
grdAlarmLog->Cells[0][i] ="";
grdAlarmLog->Cells[1][i] ="";
grdAlarmLog->Cells[2][i] ="";
grdAlarmLog->Cells[3][i] ="";
}
if(EditAlarmCodeSearch->Text != "")
{
String strStartDateTime = FormatDateTime("yyyy-mm-dd 오전 00:00:00", DateTimeAlarmStart->Date);
String strEndDateTime = FormatDateTime("yyyy-mm-dd 오전 00:00:00", DateTimeAlarmEnd->Date + 1);
String CodeName = EditAlarmCodeSearch->Text.Trim();
grdAlarmLog->RowCount = 2;
try
{
AnsiString szQuery = " select * from AlarmLog, Alarm, Groups, Recipe where Alarm_idx like " + CodeName
+ " AND Set_date BETWEEN '" + strStartDateTime + "' AND '" + strEndDateTime + "'"
+ " AND AlarmLog.Alarm_idx = Alarm.idx"
+ " AND AlarmLog.Group_idx = Groups.idx"
+ " AND AlarmLog.Group_idx = Recipe.group_idx"
+ " AND AlarmLog.Recipe_idx = Recipe.idx;";
CppSQLite3Table tblAlarmLog = dbMain.getTable(szQuery.c_str());
if (tblAlarmLog.numRows() > 0)
{
for (int row = 0; row < tblAlarmLog.numRows(); row++)
{
tblAlarmLog.setRow(row);
grdAlarmLog->Cells[0][row+1] = IntToStr(row+1);
grdAlarmLog->Cells[1][row+1] = (String)tblAlarmLog.fieldValue(7); // Start date
grdAlarmLog->Cells[2][row+1] = (String)tblAlarmLog.fieldValue(8); // End date
grdAlarmLog->Cells[3][row+1] = (String)tblAlarmLog.fieldValue(9); // Alarm Code
grdAlarmLog->Cells[4][row+1] = (String)tblAlarmLog.fieldValue(11); // Alarm Name
grdAlarmLog->RowCount = row+2;
}
}
}
catch (Exception &e)
{
}
}
}
void __fastcall TFrmAlarmDetailList::BtnTimeReadDataClick(TObject *Sender)
{
for( INT nRow = 0; nRow < grdAlarmLog->RowCount; nRow++ )
{
grdAlarmLog->Rows[ nRow+1 ]->Clear();
}
grdAlarmLog->RowCount = 2;
AnsiString strStartDateTime = FormatDateTime("yyyy-mm-dd", DateTimeAlarmStart->Date);
AnsiString strEndDateTime = FormatDateTime("yyyy-mm-dd", DateTimeAlarmEnd->Date + 1);
try
{
AnsiString szQuery = "SELECT AlarmLog.set_date, AlarmLog.clear_date, Alarm.idx, Alarm.name, Groups.name, Recipe.name"
" FROM Alarm, AlarmLog, Groups, Recipe"
" WHERE AlarmLog.set_date BETWEEN '" + strStartDateTime + "' AND '" + strEndDateTime + "'"
" AND AlarmLog.alarm_idx = Alarm.idx"
" AND AlarmLog.group_idx = Groups.idx"
" AND AlarmLog.group_idx = Recipe.group_idx"
" AND AlarmLog.recipe_idx = recipe.idx"
" ORDER BY AlarmLog.set_date DESC";
CppSQLite3Table tblAlarmLog = dbMain.getTable( szQuery.c_str() );
for( INT nRow = 0; nRow < tblAlarmLog.numRows(); nRow++ )
{
tblAlarmLog.setRow( nRow );
grdAlarmLog->Cells[ 0 ][ nRow+1 ] = nRow+1;
grdAlarmLog->Cells[ 1 ][ nRow+1 ] = tblAlarmLog.fieldValue( 0 );
grdAlarmLog->Cells[ 2 ][ nRow+1 ] = tblAlarmLog.fieldValue( 1 );
grdAlarmLog->Cells[ 3 ][ nRow+1 ] = tblAlarmLog.fieldValue( 2 );
grdAlarmLog->Cells[ 4 ][ nRow+1 ] = tblAlarmLog.fieldValue( 3 );
grdAlarmLog->RowCount = nRow+2;
}
AlarmFrequencyCount();
}
catch( Exception& e )
{
FrmCdBox->QueryMsg( "DB READ FAIL", "AlarmLog 데이터를 불러오는데 실패 하였습니다.", CD_OK );
}
}
int CDbMeter::JoinTableShow(const char* dbfilename)
{
try
{
CppSQLite3DB db;
db.open(dbfilename);
CppSQLite3Buffer bufSQL;
CppSQLite3Table t;
db.execDML("CREATE TABLE IF NOT EXISTS jointable (joinkey INTEGER PRIMARY KEY, EUI64ID CHAR(17), jointry INTEGER);");
bufSQL.format("SELECT * FROM jointable;");
t = db.getTable(bufSQL);
for ( int row = 0; row < t.numRows(); row++)
{
t.setRow(row);
for (int fld=0; fld<t.numFields(); fld++)
{
if( !t.fieldIsNull(fld))
XDEBUG("%s | ", t.fieldValue(fld));
}
XDEBUG("\r\n");
}
}
catch( CppSQLite3Exception& e )
{
XDEBUG("%s\r\n",e.errorMessage());
return e.errorCode();
}
return SQLITE_OK;
}
void TFrmAlarmDetailList::AlarmFrequencyCount()
{
String strStartDateTime = FormatDateTime("yyyy-mm-dd 오전 00:00:00", DateTimeAlarmStart->Date);
String strEndDateTime = FormatDateTime("yyyy-mm-dd 오전 00:00:00", DateTimeAlarmEnd->Date + 1);
try
{
AnsiString szQuery = "select *, count(Alarm_idx) AS Frequency from AlarmLog"
" where Set_date between '" + strStartDateTime + "' AND '" + strEndDateTime + "'"
" and Alarm_idx < 1000 group by Alarm_idx order by Frequency DESC";
CppSQLite3Table tblAlarmLog = dbMain.getTable( szQuery.c_str() );
int nFreq = 0;
String strCode = "";
int nFieldCnt = tblAlarmLog.numRows();
if(nFieldCnt > 0)
{
ChartAlarm->Series[0]->Clear();
for( int i = 0; i < nFieldCnt; i++ )
{
if(i >= 10)
{
return;
}
tblAlarmLog.setRow(i);
nFreq = ((String)tblAlarmLog.fieldValue("Frequency")).ToInt();
strCode = (String)tblAlarmLog.fieldValue("alarm_idx");
ChartAlarm->Series[0]->Add(nFreq, strCode, (Graphics::TColor)(0x20000000));
}
}
}
catch (CppSQLite3Exception& e)
{
}
}
//////////////////////////////////////////////////////////////////////////
// [3/23/2011 DHKim]
// 성공적으로 그룹테이블에 그룹이 추가되면 GroupKey를 리턴한다. 실패시 -1을 리턴
//////////////////////////////////////////////////////////////////////////
int CDbMeter::GroupAdd(const char* dbfilename, char* szGroupName)
{
int nGroupKey = -1;
try
{
CppSQLite3DB db;
db.open(dbfilename);
CppSQLite3Buffer bufSQL;
CppSQLite3Table t;
bufSQL.format("INSERT INTO groups(GroupName) VALUES(%Q);", szGroupName);
db.execDML(bufSQL);
XDEBUG("Success: INSERT Command!!\r\n");
bufSQL.clear();
bufSQL.format("SELECT GroupKey FROM groups WHERE GroupName=%Q;", szGroupName);
//CppSQLite3Query q = db.execQuery(bufSQL);
t = db.getTable(bufSQL);
if( t.numRows() == 0 )
{
XDEBUG("Failure: SELECT Command!!\r\n");
return nGroupKey;
}
else
{
// 동일 이름시 마지막에 추가된 그룹 키를 리턴한다.
int nFinalRow = t.numRows() - 1;
t.setRow(nFinalRow);
if( !t.fieldIsNull(nFinalRow) )
nGroupKey = atoi(t.fieldValue(nFinalRow));
else
{
XDEBUG("Error: GroupKey is NULL!!\r\n");
return nGroupKey;
}
XDEBUG("Success: SELECT Command!!\r\n");
}
//nGroupKey = q.getIntField("GroupKey", -1);
db.close();
}
catch( CppSQLite3Exception& e )
{
XDEBUG("%s\r\n",e.errorMessage());
return -1;
}
return nGroupKey;
}
int CDbMeter::GroupAddMember(const char* dbfilename, int nGroupKey, char* szMemberId)
{
try
{
CppSQLite3DB db;
db.open(dbfilename);
CppSQLite3Buffer bufSQL;
CppSQLite3Query q;
CppSQLite3Table t;
int nFindgroupkey = -1;
bufSQL.format("SELECT EUI64ID FROM member WHERE EUI64ID=%Q;", szMemberId);
t = db.getTable(bufSQL);
if( t.numRows() == 0 )
{
bufSQL.clear();
bufSQL.format("INSERT INTO member VALUES(%Q, 'Meter', 'none', '1.0.0', '1.0.0', 'Nomal', 'None');", szMemberId);
db.execDML(bufSQL);
}
bufSQL.clear();
bufSQL.format("SELECT * FROM groups WHERE GroupKey=%d;", nGroupKey);
t = db.getTable(bufSQL);
if( t.numRows() == 1)
nFindgroupkey = atoi(t.fieldValue(0));
else
nFindgroupkey = -1;
if( nFindgroupkey >= -1)
{
bufSQL.clear();
bufSQL.format("INSERT INTO groupmember(EUI64ID, GroupKey) VALUES(%Q, %d);", szMemberId, nGroupKey);
db.execDML(bufSQL);
}
db.close();
}
catch( CppSQLite3Exception& e )
{
XDEBUG("%s\r\n",e.errorMessage());
return -1;
}
return SQLITE_OK;
}
int main(int argc, char **argv)
{
testCppSQLite();
try
{
DrRobot::Parser file = DrRobot::Parser("files/simple.csv");
CppSQLite3DB* db = getSQLiteDB();
string statement = "create table _data ( ";
string headers = "";
//Print headers
for(int j=0;j<file.columnCount();j++){
std::cout << file.getHeaderElement(j) << "\t\t";
statement += file.getHeaderElement(j)+" TEXT,";
headers += file.getHeaderElement(j);
if(j<file.columnCount()-1)
{
headers += ",";
}
}
statement+="id INTEGER PRIMARY KEY ASC)";
std::cout<<std::endl;
std::cout<<statement<<std::endl;
db->execDML(statement.c_str());
for(int i=0;i<file.rowCount();i++) {
statement = "insert into _data Values(";
for(int j=0;j<file.columnCount();j++){
std::cout << file[i][j] << "\t\t";
statement += "\""+file[i][j]+"\",";
}
statement += "null)";
std::cout << "\n" <<statement << "\t\t";
db->execDML(statement.c_str());
std::cout<<std::endl;
}
CppSQLite3Table t = db->getTable("select * from _data;");
for (int fld = 0; fld < t.numFields(); fld++)
{
cout << t.fieldName(fld) << "|";
}
cout << endl;
for (int row = 0; row < t.numRows(); row++)
{
t.setRow(row);
for (int fld = 0; fld < t.numFields(); fld++)
{
if (!t.fieldIsNull(fld))
cout << t.fieldValue(fld) << "|";
else
cout << "NULL" << "|";
}
cout << endl;
}
}
catch (DrRobot::Error &e)
{
std::cerr << e.what() << std::endl;
}
shutDownDB();
return 0;
}
void testCppSQLite()
{
try
{
int i, fld;
time_t tmStart, tmEnd;
remove(gszFile);
CppSQLite3DB* db = getSQLiteDB();
cout << "SQLite Version: " << db->SQLiteVersion() << endl;
cout << endl << "Creating emp table" << endl;
db->execDML("create table emp(empno int, empname char(20));");
///////////////////////////////////////////////////////////////
// Execute some DML, and print number of rows affected by each one
///////////////////////////////////////////////////////////////
cout << endl << "DML tests" << endl;
int nRows = db->execDML("insert into emp values (7, 'David Beckham');");
cout << nRows << " rows inserted" << endl;
nRows = db->execDML(
"update emp set empname = 'Christiano Ronaldo' where empno = 7;");
cout << nRows << " rows updated" << endl;
nRows = db->execDML("delete from emp where empno = 7;");
cout << nRows << " rows deleted" << endl;
/////////////////////////////////////////////////////////////////
// Transaction Demo
// The transaction could just as easily have been rolled back
/////////////////////////////////////////////////////////////////
int nRowsToCreate(50000);
cout << endl << "Transaction test, creating " << nRowsToCreate;
cout << " rows please wait..." << endl;
tmStart = time(0);
db->execDML("begin transaction;");
for (i = 0; i < nRowsToCreate; i++)
{
char buf[128];
sprintf(buf, "insert into emp values (%d, 'Empname%06d');", i, i);
db->execDML(buf);
}
db->execDML("commit transaction;");
tmEnd = time(0);
////////////////////////////////////////////////////////////////
// Demonstrate CppSQLiteDB::execScalar()
////////////////////////////////////////////////////////////////
cout << db->execScalar("select count(*) from emp;")
<< " rows in emp table in ";
cout << tmEnd-tmStart << " seconds (that was fast!)" << endl;
////////////////////////////////////////////////////////////////
// Re-create emp table with auto-increment field
////////////////////////////////////////////////////////////////
cout << endl << "Auto increment test" << endl;
db->execDML("drop table emp;");
db->execDML(
"create table emp(empno integer primary key, empname char(20));");
cout << nRows << " rows deleted" << endl;
for (i = 0; i < 5; i++)
{
char buf[128];
sprintf(buf,
"insert into emp (empname) values ('Empname%06d');", i+1);
db->execDML(buf);
cout << " primary key: " << db->lastRowId() << endl;
}
///////////////////////////////////////////////////////////////////
// Query data and also show results of inserts into auto-increment field
//////////////////////////////////////////////////////////////////
cout << endl << "Select statement test" << endl;
CppSQLite3Query q = db->execQuery("select * from emp order by 1;");
for (fld = 0; fld < q.numFields(); fld++)
{
cout << q.fieldName(fld) << "(" << q.fieldDeclType(fld) << ")|";
}
cout << endl;
while (!q.eof())
{
cout << q.fieldValue(0) << "|";
cout << q.fieldValue(1) << "|" << endl;
q.nextRow();
}
///////////////////////////////////////////////////////////////
// SQLite's printf() functionality. Handles embedded quotes and NULLs
////////////////////////////////////////////////////////////////
cout << endl << "SQLite sprintf test" << endl;
CppSQLite3Buffer bufSQL;
bufSQL.format("insert into emp (empname) values (%Q);", "He's bad");
cout << (const char*)bufSQL << endl;
db->execDML(bufSQL);
bufSQL.format("insert into emp (empname) values (%Q);", NULL);
cout << (const char*)bufSQL << endl;
db->execDML(bufSQL);
////////////////////////////////////////////////////////////////////
// Fetch table at once, and also show how to
// use CppSQLiteTable::setRow() method
//////////////////////////////////////////////////////////////////
cout << endl << "getTable() test" << endl;
CppSQLite3Table t = db->getTable("select * from emp order by 1;");
for (fld = 0; fld < t.numFields(); fld++)
{
cout << t.fieldName(fld) << "|";
}
cout << endl;
for (int row = 0; row < t.numRows(); row++)
{
t.setRow(row);
for (int fld = 0; fld < t.numFields(); fld++)
{
if (!t.fieldIsNull(fld))
cout << t.fieldValue(fld) << "|";
else
cout << "NULL" << "|";
}
cout << endl;
}
////////////////////////////////////////////////////////////////////
// Test CppSQLiteBinary by storing/retrieving some binary data, checking
// it afterwards to make sure it is the same
//////////////////////////////////////////////////////////////////
cout << endl << "Binary data test" << endl;
db->execDML("create table bindata(desc char(10), data blob);");
unsigned char bin[256];
CppSQLite3Binary blob;
for (i = 0; i < sizeof bin; i++)
{
bin[i] = i;
}
blob.setBinary(bin, sizeof bin);
bufSQL.format("insert into bindata values ('testing', %Q);",
blob.getEncoded());
db->execDML(bufSQL);
cout << "Stored binary Length: " << sizeof bin << endl;
q = db->execQuery("select data from bindata where desc = 'testing';");
if (!q.eof())
{
blob.setEncoded((unsigned char*)q.fieldValue("data"));
cout << "Retrieved binary Length: "
<< blob.getBinaryLength() << endl;
}
const unsigned char* pbin = blob.getBinary();
for (i = 0; i < sizeof bin; i++)
{
if (pbin[i] != i)
{
cout << "Problem: i: ," << i << " bin[i]: "
<< pbin[i] << endl;
}
}
/////////////////////////////////////////////////////////
// Pre-compiled Statements Demo
/////////////////////////////////////////////////////////////
cout << endl << "Transaction test, creating " << nRowsToCreate;
cout << " rows please wait..." << endl;
db->execDML("drop table emp;");
db->execDML("create table emp(empno int, empname char(20));");
tmStart = time(0);
db->execDML("begin transaction;");
CppSQLite3Statement stmt = db->compileStatement(
"insert into emp values (?, ?);");
for (i = 0; i < nRowsToCreate; i++)
{
char buf[16];
sprintf(buf, "EmpName%06d", i);
stmt.bind(1, i);
stmt.bind(2, buf);
stmt.execDML();
stmt.reset();
}
db->execDML("commit transaction;");
tmEnd = time(0);
cout << db->execScalar("select count(*) from emp;")
<< " rows in emp table in ";
cout << tmEnd-tmStart << " seconds (that was even faster!)" << endl;
cout << endl << "End of tests" << endl;
}
catch (CppSQLite3Exception& e)
{
cerr << e.errorCode() << ":" << e.errorMessage() << endl;
}
}
int CDbMeter::GroupShowCommand(const char* dbfilename, int nGroupKey, GROUPCOMMANDINFO *pGroupCommandinfo)
{
try
{
CppSQLite3DB db;
db.open(dbfilename);
CppSQLite3Buffer bufSQL;
CppSQLite3Table t;
char szTime[16] = {0,};
char szTmpTime[5] = {0,};
if (0 < nGroupKey)
bufSQL.format("SELECT * FROM command WHERE GroupKey=%d;", nGroupKey);
else
bufSQL.format("SELECT * FROM command;");
t = db.getTable(bufSQL);
for( int fld = 0; fld < t.numFields(); fld++)
XDEBUG("%s | ", t.fieldName(fld));
XDEBUG("\r\n");
for ( int row = 0; row < t.numRows(); row++)
{
t.setRow(row);
for (int fld=0; fld<t.numFields(); fld++)
{
if( !t.fieldIsNull(fld))
{
switch(fld)
{
case 0:
pGroupCommandinfo->nCommandKey = atoi( t.fieldValue(fld) );
break;
case 1:
pGroupCommandinfo->nGroupKey = atoi( t.fieldValue(fld) );
break;
case 2:
StrToEUI64( t.fieldValue(fld), &(pGroupCommandinfo->id) );
break;
case 3:
memcpy( pGroupCommandinfo->pGroupName, t.fieldValue(fld), strlen(t.fieldValue(fld)) );
break;
case 4:
pGroupCommandinfo->nCommandState = atoi( t.fieldValue(fld));
break;
case 5:
VARAPI_StringToOid( t.fieldValue(fld), &(pGroupCommandinfo->commandOID) );
break;
case 6: // Create TimeStamp
memcpy(szTime, t.fieldValue(fld), strlen(t.fieldValue(fld)));
memcpy(szTmpTime, szTime, 4); //Year
pGroupCommandinfo->commandCreateTime.year = atoi(szTmpTime);
memcpy(szTmpTime, &(szTime[4]), 2 ); //Month
pGroupCommandinfo->commandCreateTime.mon = atoi(szTmpTime);
memcpy(szTmpTime, &(szTime[6]), 2 ); //Day
pGroupCommandinfo->commandCreateTime.day = atoi(szTmpTime);
memcpy(szTmpTime, &(szTime[8]), 2 ); //Hour
pGroupCommandinfo->commandCreateTime.hour = atoi(szTmpTime);
memcpy(szTmpTime, &(szTime[10]), 2 ); //Min
pGroupCommandinfo->commandCreateTime.min = atoi(szTmpTime);
memcpy(szTmpTime, &(szTime[12]), 2 ); //Sec
pGroupCommandinfo->commandCreateTime.sec = atoi(szTmpTime);
break;
case 7:
memcpy(szTime, t.fieldValue(fld), strlen(t.fieldValue(fld)));
memcpy(szTmpTime, szTime, 4); //Year
pGroupCommandinfo->commandExecTime.year = atoi(szTmpTime);
memcpy(szTmpTime, &(szTime[4]), 2 ); //Month
pGroupCommandinfo->commandExecTime.mon = atoi(szTmpTime);
memcpy(szTmpTime, &(szTime[6]), 2 ); //Day
pGroupCommandinfo->commandExecTime.day = atoi(szTmpTime);
memcpy(szTmpTime, &(szTime[8]), 2 ); //Hour
pGroupCommandinfo->commandExecTime.hour = atoi(szTmpTime);
memcpy(szTmpTime, &(szTime[10]), 2 ); //Min
pGroupCommandinfo->commandExecTime.min = atoi(szTmpTime);
memcpy(szTmpTime, &(szTime[12]), 2 ); //Sec
pGroupCommandinfo->commandExecTime.sec = atoi(szTmpTime);
break;
case 8:
pGroupCommandinfo->nResult = 0;
break;
case 9:
break;
}
XDEBUG("%s | ", t.fieldValue(fld));
}
}
XDEBUG("\r\n");
}
db.close();
}
catch( CppSQLite3Exception& e )
{
XDEBUG("%s\r\n",e.errorMessage());
return e.errorCode();
}
return SQLITE_OK;
}
int CDbMeter::GroupCommand(const char* dbfilename, int nGroupKey, vector<char*> vbufMember, MIBValue* pValue, int cnt)
{
try
{
CppSQLite3DB db;
db.open(dbfilename);
CppSQLite3Buffer bufSQL;
CppSQLite3Query q;
CppSQLite3Table t;
FILE *fp;
char szCmd[12] = {0, };
char szFileName[20] = {0, };
bufSQL.format("SELECT groups.GroupKey, groups.GroupName, groupmember.EUI64ID FROM groups LEFT OUTER JOIN groupmember ON groups.GroupKey=groupmember.GroupKey WHERE groupmember.GroupKey=%d;", nGroupKey);
t = db.getTable(bufSQL);
for ( int row = 0; row < t.numRows(); row++)
{
t.setRow(row);
if( !t.fieldIsNull(1))
{
VARAPI_OidToString(&pValue[1].stream.id, szCmd);
bufMember.push_back((char*)t.fieldValue(2));
XDEBUG("%d | %s | %s\r\n", atoi(t.fieldValue(0)), t.fieldValue(1), t.fieldValue(2) );
bufSQL.clear();
bufSQL.format("SELECT datetime('now', 'localtime');");
q = db.execQuery(bufSQL);
sprintf(szFileName,"/app/sqlite/%s.txt", t.fieldValue(2));
fp = fopen(szFileName,"a+t");
for (int paramcnt=2; paramcnt<cnt; paramcnt++)
{
if( VARAPI_OidCompare( &(pValue[paramcnt].oid), "1.11") == 0 )
{
fprintf(fp, "%s", pValue[paramcnt].stream.p);
}
else if( VARAPI_OidCompare( &(pValue[paramcnt].oid), "1.5") == 0 || VARAPI_OidCompare( &(pValue[paramcnt].oid), "1.8") == 0 )
{
fprintf(fp, "%d", pValue[paramcnt].stream.u16);
}
else if( VARAPI_OidCompare( &(pValue[paramcnt].oid), "1.6") == 0 || VARAPI_OidCompare( &(pValue[paramcnt].oid), "1.9") == 0)
{
fprintf(fp, "%d", pValue[paramcnt].stream.u32);
}
else
{
fprintf(fp, "%d", pValue[paramcnt].stream.u8);
}
if(paramcnt < cnt - 1)
fprintf(fp, "|");
}
fprintf(fp,"\n");
fclose(fp);
XDEBUG("Success: File write!!\r\n");
bufSQL.clear();
XDEBUG("CMD: %s\r\n", szCmd);
bufSQL.format("INSERT INTO command(GroupKey, EUI64ID, GroupName, CommandState, Command, CommandCreateTime, ResultErrorCode, ParamPathInfo) VALUES(%d, %Q, %Q, %d, %s, %Q, %d, %Q);", nGroupKey, t.fieldValue(2), t.fieldValue(1), 1, szCmd, q.fieldValue(0), 0, "CmdParamPathInfo.txt" );
db.execDML(bufSQL);
}
}
db.close();
}
catch( CppSQLite3Exception& e )
{
XDEBUG("%s\r\n",e.errorMessage());
return e.errorCode();
}
return SQLITE_OK;
}
