C++ CppSQLite3Table::numRows方法代码示例

int CDbMeter::GroupDelete(const char* dbfilename, int nGroupKey)
{
	try
	{
		CppSQLite3DB db;
		db.open(dbfilename);
		// 그룹테이블에 그룹이름이 있느지 확인
		CppSQLite3Buffer bufSQL;
		//CppSQLite3Query q;
		CppSQLite3Table t;

		bufSQL.format("SELECT GroupKey FROM groups WHERE GroupKey=%d;", nGroupKey);
		t = db.getTable(bufSQL);

		if( t.numRows() == 0 )
		{
			XDEBUG("ERROR: %d GroupKey doesn't exist in Groups Table!!\r\n", nGroupKey);
			return IF4ERR_GROUP_NAME_NOT_EXIST;
		}
		else
		{
			bufSQL.clear();
			bufSQL.format("DELETE FROM groups WHERE GroupKey=%d;", nGroupKey);
			db.execDML(bufSQL);

			XDEBUG("%d GroupKey deleted in Groups Table!!\r\n", nGroupKey);
			//bufSQL.format("SELECT Groups.GroupName FROM groupmember LEFT OUTER JOIN groups ON groupmember.GroupKey=groups.GroupKey LEFT OUTER JOIN member ON groupmember.EUI64ID=member.EUI64ID WHERE groups.GroupName = %s;", szGroupName);
		}

		bufSQL.clear();
		bufSQL.format("SELECT GroupKey FROM groupmember WHERE GroupKey=%d;", nGroupKey);
		t = db.getTable(bufSQL);

		if( t.numRows() == 0 )
		{
			XDEBUG("ERROR: %d GroupKey doesn't exist!! in Groupmember Table!!\r\n", nGroupKey);
			return IF4ERR_GROUP_NAME_NOT_EXIST;
		}
		else
		{
			bufSQL.clear();
			bufSQL.format("DELETE FROM groupmember WHERE GroupKey=%d;", nGroupKey);
			db.execQuery(bufSQL);
			XDEBUG("%d GroupKey deleted in Groupmember Table!!\r\n", nGroupKey);
		}
		db.close();
	}
	catch( CppSQLite3Exception& e )
	{
		XDEBUG("%s\r\n",e.errorMessage());
		return e.errorCode();
	}
	return SQLITE_OK;
}

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

}

//////////////////////////////////////////////////////////////////////////
//  [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;
}

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

uint32 CBrainMemory::GetAllPartOfSpeech(int64 ParentID){
	   CppSQLite3Buffer SQL;
       char a[30],b[30],c[30];
	   
	   uint32 PartOfSpeech = 0;
//	   if(!RBrainHasTable(ID))return 0;

	   ToRBrain(ParentID);
	   int64toa(ParentID,a);
	   int64toa(PARTOFSPEECH_START,b);
	   int64toa(PARTOFSPEECH_END,c);

	   SQL.format("select %s from \"%s\" where %s > \"%s\" AND %s< \"%s\";",
						RB_SPACE_TYPE,
			            a,
						RB_SPACE_TYPE,
						b, 
                        RB_SPACE_TYPE,
						c
						);
	   CppSQLite3Table t = BrainDB.getTable(SQL);

	   for (int row = 0; row < t.numRows(); row++)
	   {
			t.setRow(row);
			int64 type = t.getInt64Field(0);
            PartOfSpeech |= (uint32)((type-PARTOFSPEECH_START));  
	   }
	   return PartOfSpeech;
}

int CDbMeter::JoinTableDelete(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);

		if( t.numRows() == 0 )
		{
			XDEBUG("Failure: Don't have JoinTable\r\n");
		}
		else
		{
			db.execDML("DROP TABLE joinTable;");
		}
	}
	catch( CppSQLite3Exception& e )
	{
		XDEBUG("%s\r\n",e.errorMessage());
		return e.errorCode();
	}
	return SQLITE_OK;
}

int32 CLogicThread::GetActionRoom(int64 ParentID,ClauseLogicSense* cls){
	   CppSQLite3Buffer SQL;
       char a[30],b[30];
	   
	   uint32 PartOfSpeech = 0;
//	   if(!RBrainHasTable(ID))return 0;

	   map<int64,MeaningPos>& RoomList = cls->ClauseMeaning;

	   ToRBrain(ParentID);

	   int64toa(ParentID,a);
	   int64toa(MEMORY_INSTINCT,b);
	   SQL.format("select %s,%s from \"%s\" where %s = \"%s\" ;",
						RB_SPACE_ID,
						RB_SPACE_VALUE,
			            a,
						RB_SPACE_TYPE,
						b
						);
	   CppSQLite3Table t = BrainDB.getTable(SQL);

	   for (int row = 0; row < t.numRows(); row++)
	   {
			t.setRow(row);
			int64 ID = t.getInt64Field(0);
			int64 Value = t.getInt64Field(1);
			MeaningPos Pos;
			Pos.RoomID = ID;
			Pos.ParamTokenPos = cls->NextTokenPos;
            RoomList[Value] = Pos;
	   }
	   return RoomList.size();	
}

int32 CBrainMemory::GetAllPartOfSpeechRoom(int64 ParentID,map<int64,int64>& RoomList)
{
	   CppSQLite3Buffer SQL;
       char a[30],b[30],c[30];
	   
	   uint32 PartOfSpeech = 0;
//	   if(!RBrainHasTable(ID))return 0;

	   ToRBrain(ParentID);

	   int64toa(ParentID,a);
	   int64toa(PARTOFSPEECH_START,b); 
	   int64toa(PARTOFSPEECH_END,c);

	   SQL.format("select %s,%s from \"%s\" where %s > \"%s\" AND %s< \"%s\";",
						RB_SPACE_ID,
						RB_SPACE_TYPE,
			            a,
						RB_SPACE_TYPE,
						b,
                        RB_SPACE_TYPE,
						c
						);
	   CppSQLite3Table t = BrainDB.getTable(SQL);

	   for (int row = 0; row < t.numRows(); row++)
	   {
			t.setRow(row);
			int64 ID = t.getInt64Field(0);
			int64 Type = t.getInt64Field(1);
            RoomList[ID] = Type;
	   }
	   return RoomList.size();
}

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

int CDbMeter::JoinTableAdd(const char* dbfilename, char* szID)
{
	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 WHERE EUI64ID=%Q", szID);
		t = db.getTable(bufSQL);
		if( t.numRows() == 0 )
		{
			bufSQL.clear();
			bufSQL.format("INSERT INTO jointable(EUI64ID, jointry) VALUES(%Q, %d);", szID, 0);
			db.execDML(bufSQL);
		}
		else
			XDEBUG("Jointable ID Duplicate!!\r\n");

		db.close();
	}
	catch( CppSQLite3Exception& e )
	{
		XDEBUG("%s\r\n",e.errorMessage());
		return e.errorCode();
	}
	return SQLITE_OK;
}

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

int64 CBrainMemory::HasMeaningRoom(int64 ParentTable,int64 Meaning,int64 MeaningType){
	CppSQLite3Buffer SQL;
	char a[30],b[30];
	   
	if(!RBrainHasTable(ParentTable))return 0;
	ToRBrain(ParentTable);

	int64toa(ParentTable,a);
	int64toa(MeaningType,b);
	SQL.format("select %s, %s  from \"%s\" where %s = \"%s\";",
		   RB_SPACE_ID,
		   RB_SPACE_VALUE,
		   a,
		   RB_SPACE_TYPE,
		   b
		   );
	CppSQLite3Table t = BrainDB.getTable(SQL);

	if(Meaning == 0){
		for (int row = 0; row < t.numRows(); row++)
		{
			t.setRow(row);
			int64 ID   = t.getInt64Field(0);
			int64 Value = t.getInt64Field(1);
            if(ID == Value){
				return ID; //已经记忆过了，直接返回
			}
		}
	}else{
		for (int row = 0; row < t.numRows(); row++)
		{
			t.setRow(row);
			int64 ID   = t.getInt64Field(0);
			int64 Value = t.getInt64Field(1);
            if(Value = Meaning){
				return ID; //已经记忆过了，直接返回
			}
		}	
	}
	return 0;
}

void	TfrmUseSkipViewer::LoadUseSkipNameFromDB()
{       
    CppSQLite3DB db;
	String szSelectQuery = "SELECT * FROM UseSkip";

	try
	{
		if( FileExists( g_MainDBPath ) == true )
		{
            db.open( AnsiString( g_MainDBPath ).c_str() );

            CppSQLite3Table table = db.getTable( AnsiString( szSelectQuery ).c_str() );

            if( table.numRows() != 0 )
            {
                for( int row = 0; row < table.numRows(); row++ )
                {
                    table.setRow( row );
                    String szName = table.getStringField( "name", "NULL" );
                    
                    if( szName == "NULL" || szName == "" )
                    {
                        szName = "UseSkip" + IntToStr( row );
                    }
                    
                    ComboBoxUseSkip->AddItem( szName, NULL );
                }
    		}
            db.close();
		}
	}
	catch(Exception &e)
	{
		db.close();
		ShowMessage("Use Skip DB select error! : " + e.Message);
	}       
}

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

}

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)
	{

	}
}