OTS/OTSCPP/OTSRptCalculate/DTL/SegmentDB.cpp

#pragma once
#include "stdafx.h"
#include "SegmentDB.h"
#include "SegmentTable.h"

namespace OTSSQLITE
{
	CSegmentDB::CSegmentDB(CDBStoreBasePtr a_datastore)	
	{
		m_tableInfo.reset(new CSegmentTable());
		myDB = CreateNewSQLiteDB(a_datastore,m_tableInfo);
	}

	CSegmentDB::~CSegmentDB()
	{
	}

	COTSFeaturePtr CSegmentDB::GetFeatureById(const long a_nXrayId, const long a_nFieldId, const long a_nSegmentSize)
	{
		COTSFeaturePtr pFeatureNew = nullptr;

		if (!m_listParticle.empty())
		{
			for (auto pParticle : m_listParticle)
			{
				COTSFeaturePtr pFeature = pParticle->GetFeature();

				ASSERT(pFeature);
				if (!pFeature)
				{
					return nullptr;
				}

				if (pParticle->GetAnalysisId() == (DWORD)a_nXrayId && pParticle->GetFieldId() == a_nFieldId && (int)pFeature->GetSegmentsList().size() == a_nSegmentSize)
				{
					pFeatureNew = COTSFeaturePtr(new COTSFeature(*pFeature.get()));
				}
			}
		}
		else
		{
			// read element list
			for (int i = 0; i < a_nSegmentSize; i++)
			{
				auto tableQuery = GetQueryById(a_nXrayId, a_nFieldId, i, a_nSegmentSize);
				ASSERT(tableQuery);
				if (!tableQuery)
				{
					return nullptr;
				}

				COTSParticlePtr pParticle = ReadParticleInfo(tableQuery);
				ASSERT(pParticle);
				if (!pParticle)
				{
					return nullptr;
				}

				COTSFeaturePtr pFeature = pParticle->GetFeature();

				ASSERT(pFeature);
				if (!pFeature)
				{
					return nullptr;
				}

				pFeatureNew = COTSFeaturePtr(new COTSFeature(*pFeature.get()));
			}

		}

		return pFeatureNew;
	}

	COTSParticleList& CSegmentDB::GetParticleInfoList(const BOOL a_bForce/* = FALSE*/)
	{
		if (a_bForce)
		{
			m_listParticle.clear();
		}

		if (m_listParticle.size() == 0)
		{
			ReadParticleInfoList();
		}

		return m_listParticle;
	}

	BOOL CSegmentDB::SaveFeature(const COTSParticleList& a_ParticleList)
	{
		if (!Init())
		{
			ASSERT(FALSE);
			return FALSE;
		}

		auto tableInfoPtr = GetTableInfo();
		ASSERT(tableInfoPtr);
		if (!tableInfoPtr)
		{
			return FALSE;
		}

		auto datastorePtr = GetDatastore();
		ASSERT(datastorePtr);
		if (!datastorePtr)
		{
			return FALSE;
		}

		CString sInsertFormat = tableInfoPtr->GetInsertCommandFormatString(TRUE);
		CString sSQLCommand;

		for (auto pParticle : a_ParticleList)
		{
			
			COTSFeaturePtr pFeature = pParticle->GetFeature();

			ASSERT(pFeature);
			if (!pFeature)
			{
				return FALSE;
			}

			COTSSegmentsList listSegments = pFeature->GetSegmentsList();
			int nSize = (int)listSegments.size();
			int nSegmentIndex = 0;
			for (auto pSegment : listSegments)
			{
				sSQLCommand.Format(sInsertFormat,
					pParticle->GetAnalysisId(),
					pParticle->GetFieldId(),
					nSegmentIndex,
					nSize,
					pSegment->GetStart(),
					pSegment->GetHeight(),
					pSegment->GetLength()),
					pParticle->GetParticleId();
				if (!datastorePtr->RunCommand(sSQLCommand))
				{
					LogErrorTrace(__FILE__, __LINE__, _T("Insert segment(%d:%d:%d:%d) failed: %s command error"),
						pParticle->GetAnalysisId(),
						pParticle->GetFieldId(),
						nSegmentIndex,
						nSize,
						pSegment->GetStart(),
						pSegment->GetHeight(),
						pSegment->GetLength(),
						pParticle->GetParticleId(),
						sSQLCommand);

					ASSERT(FALSE);
					return FALSE;
				}

				nSegmentIndex++;
			}

		}

		return TRUE;
	}

	BOOL CSegmentDB::SaveFeature(COTSParticlePtr a_pParticle)
	{
		if (!Init())
		{
			ASSERT(FALSE);
			return FALSE;
		}

		auto tableInfoPtr = GetTableInfo();
		ASSERT(tableInfoPtr);
		if (!tableInfoPtr)
		{
			return FALSE;
		}

		auto datastorePtr = GetDatastore();
		ASSERT(datastorePtr);
		if (!datastorePtr)
		{
			return FALSE;
		}

		CString sInsertFormat = tableInfoPtr->GetInsertCommandFormatString(TRUE);
		CString sSQLCommand;

		ASSERT(a_pParticle);
		if (!a_pParticle)
		{
			return FALSE;
		}

		COTSFeaturePtr pFeature = a_pParticle->GetFeature();
		COTSSegmentsList listSegments = pFeature->GetSegmentsList();
		
		int nSize = (int)listSegments.size();
		int nSegmentIndex = 0;
		for (auto pSegment : listSegments)
		{
			sSQLCommand.Format(sInsertFormat,
				a_pParticle->GetAnalysisId(),
				a_pParticle->GetFieldId(),
				nSegmentIndex,
				nSize,
				pSegment->GetStart(),
				pSegment->GetHeight(),
				pSegment->GetLength(),
			    a_pParticle->GetParticleId ());

			if (!datastorePtr->RunCommand(sSQLCommand))
			{
				LogErrorTrace(__FILE__, __LINE__, _T("Insert element(%d:%d:%d:%d) failed: %s command error"),
					a_pParticle->GetAnalysisId(),
					a_pParticle->GetFieldId(),
					nSegmentIndex,
					nSize,
					pSegment->GetStart(),
					pSegment->GetHeight(),
					pSegment->GetLength(),
					a_pParticle->GetParticleId(),
					sSQLCommand);

				ASSERT(FALSE);
				return FALSE;
			}

			nSegmentIndex++;
		}

		return TRUE;
	}

	BOOL CSegmentDB::DeleteFeatureById(const long a_nFieldId, const long a_nXrayId)
	{
		if (!m_listParticle.empty())
		{
			std::remove_if(m_listParticle.begin(), m_listParticle.end(), [a_nFieldId, a_nXrayId](const COTSParticlePtr p) { return(p->GetAnalysisId() == (DWORD)a_nXrayId) && (p->GetFieldId() == (DWORD)a_nFieldId); });
		}

		auto tableInfoPtr = GetTableInfo();
		ASSERT(tableInfoPtr);
		if (!tableInfoPtr)
		{
			return FALSE;
		}

		auto datastorePtr = GetDatastore();
		ASSERT(datastorePtr);
		if (!datastorePtr)
		{
			return FALSE;
		}

		CString sTableName = tableInfoPtr->GetTableName();
		if (!datastorePtr->IsTableExists(sTableName))
		{
			LogTrace(__FILE__, __LINE__, _T("Table %s not exist"), sTableName);
			return TRUE;
		}

		CString sXrayIdColumnName = tableInfoPtr->GetColumnName((int)CSegmentTable::ColumnID::N_XRAY_INDEX - (int)CSegmentTable::ColumnID::MIN);
		CString sFieldIdColumnName = tableInfoPtr->GetColumnName((int)CSegmentTable::ColumnID::N_FIELD_ID - (int)CSegmentTable::ColumnID::MIN);

		CString sSQLCommand;
		sSQLCommand.Format(_T("DELETE FROM \'%s\' WHERE %s = %d AND %s = %d AND %s = %d AND %s = %d;"),
			(LPCTSTR)tableInfoPtr->GetTableName(),
			(LPCTSTR)sXrayIdColumnName,
			a_nXrayId,
			(LPCTSTR)sFieldIdColumnName,
			a_nFieldId);

		return datastorePtr->RunCommand(sSQLCommand);
	}

	CDBTableBasePtr CSegmentDB::GetTableInfo()
	{
		/*if (!m_tableInfo)
		{
			m_tableInfo.reset(new CSegmentTable);
		}
*/
		return m_tableInfo;
	}



	BOOL CSegmentDB::ReadParticleInfoList()
	{
		auto tableInfoPtr = GetTableInfo();
		ASSERT(tableInfoPtr);
		if (!tableInfoPtr)
		{
			return FALSE;
		}

		auto query = GetTableQuery();
		ASSERT(query);
		if (!query)
		{
			return FALSE;
		}

		m_listParticle = ReadParticleInfoList(query);

		return TRUE;
	}

	COTSParticleList CSegmentDB::ReadParticleInfoList(CDBQueryBasePtr a_query)
	{
		COTSParticleList listParticle;

		int nRowId = 0;
		int nWrongItems = 0;
		while (!a_query->IsEOF())
		{
			auto ParticleInfo = ReadParticleInfo(a_query);	//current x-ray point		

			if (!ParticleInfo)
			{
				LogErrorTrace(__FILE__, __LINE__, _T("Read particle info item failed: row id: %d"), nRowId);
				nWrongItems++;
			}
			else
			{
				if (!listParticle.empty())
				{
					int nXrayId = ParticleInfo->GetAnalysisId();
					int nFieldId = ParticleInfo->GetFieldId();
					
					COTSFeaturePtr pFeature = ParticleInfo->GetFeature();

					ASSERT(pFeature);
					if (!pFeature)
					{
						return listParticle;
					}

					COTSSegmentsList listSegmentNew = pFeature->GetSegmentsList();					
					
					int nIndex = 0;

					for (auto pParticle : listParticle)
					{
						
						listParticle.erase(listParticle.begin() + nIndex);

						if ((pParticle->GetAnalysisId() == nXrayId) 
							&& (pParticle->GetFieldId() == nFieldId))
						{
							COTSFeaturePtr pFeature = pParticle->GetFeature();

							ASSERT(pFeature);
							if (!pFeature)
							{
								return listParticle;
							}

							COTSSegmentsList listSegmentOld = pFeature->GetSegmentsList();

							for (auto pSegment : listSegmentNew)
							{
								listSegmentOld.push_back(COTSSegmentPtr(new COTSSegment(*pSegment.get())));
							}

							pFeature->SetSegmentsList(listSegmentOld);

							ParticleInfo->SetFeature(pFeature);

							break;
						}
						nIndex++;
					}

				}

				listParticle.push_back(ParticleInfo);
			}

			a_query->NextRow();
			nRowId++;
		}

		return listParticle;
	}

	COTSParticlePtr CSegmentDB::ReadParticleInfo(CDBQueryBasePtr a_query)
	{
		int nCol;
		COTSParticlePtr pParticle(new COTSParticle());
	
		int nXrayIdNow;
		int nFieldIdNow;

		nCol = (int)CSegmentTable::ColumnID::N_XRAY_INDEX - (int)CSegmentTable::ColumnID::MIN;
		nXrayIdNow = a_query->GetColIntValue(nCol, -1);
		pParticle->SetAnalysisId(nXrayIdNow);

		nCol = (int)CSegmentTable::ColumnID::N_FIELD_ID - (int)CSegmentTable::ColumnID::MIN;
		nFieldIdNow = a_query->GetColIntValue(nCol, -1);
		pParticle->SetFieldId(nFieldIdNow);

		nCol = (int)CSegmentTable::ColumnID::N_SEGMENT_ID - (int)CSegmentTable::ColumnID::MIN;
		int nSegmentIndex = a_query->GetColIntValue(nCol, -1);

		nCol = (int)CSegmentTable::ColumnID::N_SEGMENT_TOTAL - (int)CSegmentTable::ColumnID::MIN;
		int nSegmentTotal = a_query->GetColIntValue(nCol, -1);
		//pParticle->SetElementNum(nElementTotal);

		if (nSegmentIndex > nSegmentTotal - 1)
		{
			return nullptr;
		}

		COTSSegmentPtr pSegment = COTSSegmentPtr(new COTSSegment());

		nCol = (int)CSegmentTable::ColumnID::N_START - (int)CSegmentTable::ColumnID::MIN;
		int nStart = a_query->GetColIntValue(nCol, -1);
		pSegment->SetStart(nStart);

		nCol = (int)CSegmentTable::ColumnID::N_HEIGHT - (int)CSegmentTable::ColumnID::MIN;
		int nHeight = a_query->GetColIntValue(nCol, -1);
		pSegment->SetHeight(nHeight);

		nCol = (int)CSegmentTable::ColumnID::N_LENGTH - (int)CSegmentTable::ColumnID::MIN;
		int nLength = a_query->GetColIntValue(nCol, -1);
		pSegment->SetLength(nLength);

		COTSSegmentsList listSegment;
		listSegment.push_back(pSegment);

		a_query->NextRow();

		int nXrayNew, nFieldIdNew;

		int nRowId = 0;
		while (!a_query->IsEOF())
		{
			nCol = (int)CSegmentTable::ColumnID::N_XRAY_INDEX - (int)CSegmentTable::ColumnID::MIN;
			nXrayNew = a_query->GetColIntValue(nCol, -1);

			nCol = (int)CSegmentTable::ColumnID::N_FIELD_ID - (int)CSegmentTable::ColumnID::MIN;
			nFieldIdNew = a_query->GetColIntValue(nCol, -1);

			if (nXrayNew == nXrayIdNow && nFieldIdNew == nFieldIdNow)
			{
				nCol = (int)CSegmentTable::ColumnID::N_SEGMENT_ID - (int)CSegmentTable::ColumnID::MIN;
				int nSegmentIndex = a_query->GetColIntValue(nCol, -1);

				nCol = (int)CSegmentTable::ColumnID::N_SEGMENT_TOTAL - (int)CSegmentTable::ColumnID::MIN;
				int nSegmentTotal = a_query->GetColIntValue(nCol, -1);

				if (nSegmentIndex > nSegmentTotal - 1)
				{
					return nullptr;
				}

				COTSSegmentPtr pSegment = COTSSegmentPtr(new COTSSegment());

				nCol = (int)CSegmentTable::ColumnID::N_START - (int)CSegmentTable::ColumnID::MIN;
				int nStart = a_query->GetColIntValue(nCol, -1);
				pSegment->SetStart(nStart);

				nCol = (int)CSegmentTable::ColumnID::N_HEIGHT - (int)CSegmentTable::ColumnID::MIN;
				int nHeight = a_query->GetColIntValue(nCol, -1);
				pSegment->SetHeight(nHeight);

				nCol = (int)CSegmentTable::ColumnID::N_LENGTH - (int)CSegmentTable::ColumnID::MIN;
				int nLength = a_query->GetColIntValue(nCol, -1);
				pSegment->SetLength(nLength);

				listSegment.push_back(pSegment);

				if (nSegmentIndex == nSegmentTotal - 1)
				{
					break;
				}
			}
			else
			{
				continue;
			}

			a_query->NextRow();
			nRowId++;
		}

		COTSFeaturePtr pFeature = COTSFeaturePtr(new COTSFeature());
		pFeature->SetSegmentsList(listSegment);

		pParticle->SetFeature(pFeature);

		return pParticle;
	}

	CDBQueryBasePtr CSegmentDB::GetQueryById(const long a_nXrayId, const long a_nFieldId, const long a_nSegmentId, const long a_nSegmentNum)
	{
		CDBQueryBasePtr query;
		auto datastorePtr = GetDatastore();
		ASSERT(datastorePtr);
		if (!datastorePtr)
		{
			return query;
		}

		auto tableInfoPtr = GetTableInfo();
		ASSERT(tableInfoPtr);
		if (!tableInfoPtr)
		{
			return query;
		}

		CString sXrayIdColumnName = tableInfoPtr->GetColumnName((int)CSegmentTable::ColumnID::N_XRAY_INDEX - (int)CSegmentTable::ColumnID::MIN);
		CString sFieldIdColumnName = tableInfoPtr->GetColumnName((int)CSegmentTable::ColumnID::N_FIELD_ID - (int)CSegmentTable::ColumnID::MIN);
		CString sElementIdColumnName = tableInfoPtr->GetColumnName((int)CSegmentTable::ColumnID::N_SEGMENT_ID - (int)CSegmentTable::ColumnID::MIN);
		CString sElementNumColumnName = tableInfoPtr->GetColumnName((int)CSegmentTable::ColumnID::N_SEGMENT_TOTAL - (int)CSegmentTable::ColumnID::MIN);

		CString sSQLCommand;
		sSQLCommand.Format(_T("SELECT * FROM \'%s\' WHERE %s = %d AND %s = %d AND %s = %d AND %s = %d;"),
			(LPCTSTR)tableInfoPtr->GetTableName(),
			(LPCTSTR)sFieldIdColumnName,
			a_nFieldId,
			(LPCTSTR)sXrayIdColumnName,
			a_nXrayId,
			(LPCTSTR)sElementIdColumnName,
			a_nSegmentId,
			(LPCTSTR)sElementNumColumnName,
			a_nSegmentNum);

		query = datastorePtr->QueryByCommand(sSQLCommand);
		ASSERT(query);
		if (!query || !query->IsValid())
		{
			LogErrorTrace(__FILE__, __LINE__, _T("Invalid quary command (%s)."), (LPCTSTR)sSQLCommand);
			ASSERT(FALSE);
			return (CDBQueryBasePtr());
		}

		// do the table related valid checking
		if (query->GetColCount() != GetTableInfo()->GetColumnCount())
		{
			LogErrorTrace(__FILE__, __LINE__, _T("query col num value is %d, but not %d"), query->GetColCount(), GetTableInfo()->GetColumnCount());
			ASSERT(FALSE);
			return (CDBQueryBasePtr());
		}

		return query;
	}

	CDBQueryBasePtr CSegmentDB::GetQueryOfAllRecord()
	{

		CDBQueryBasePtr query;
		auto datastorePtr = GetDatastore();
		ASSERT(datastorePtr);
		if (!datastorePtr)
		{
			return query;
		}

		auto tableInfoPtr = GetTableInfo();
		ASSERT(tableInfoPtr);
		if (!tableInfoPtr)
		{
			return query;
		}


		CString sSQLCommand;
		sSQLCommand.Format(_T("SELECT * FROM \'%s\';"),
			(LPCTSTR)tableInfoPtr->GetTableName());

		query = datastorePtr->QueryByCommand(sSQLCommand);
		ASSERT(query);
		if (!query || !query->IsValid())
		{
			LogErrorTrace(__FILE__, __LINE__, _T("Invalid quary command (%s)."), (LPCTSTR)sSQLCommand);
			ASSERT(FALSE);
			return (CDBQueryBasePtr());
		}

		// do the table related valid checking
		if (query->GetColCount() != GetTableInfo()->GetColumnCount())
		{
			LogErrorTrace(__FILE__, __LINE__, _T("query col num value is %d, but not %d"), query->GetColCount(), GetTableInfo()->GetColumnCount());
			ASSERT(FALSE);
			return (CDBQueryBasePtr());
		}

		return query;
	}
	bool CSegmentDB::GetAllSegmentsRecord(std::map<std::vector <int>, COTSSegmentsList>& mapSegments)
	{
		//std::map<std::vector <int>, COTSSegmentsList> mapSegments;//the vector contains two value: fieldId and XrayId
		auto allRecords = this->GetQueryOfAllRecord();

		while (!allRecords->IsEOF())
		{			
			int curFldId= allRecords->GetColIntValue((int)CSegmentTable::ColumnID::N_FIELD_ID);
			int curParticleId = allRecords->GetColIntValue((int)CSegmentTable::ColumnID::N_PARTICLE_ID);
			std::vector <int> fldvec;
			fldvec.push_back(curFldId);
			fldvec.push_back(curParticleId);
			auto itr = mapSegments.find(fldvec);
			if (itr == mapSegments.end())
			{
				
				COTSSegmentsList segments;
				COTSSegmentPtr segment = COTSSegmentPtr(new COTSSegment());
				segment->SetStart(allRecords->GetColIntValue((int)CSegmentTable::ColumnID::N_START));
				segment->SetHeight(allRecords->GetColIntValue((int)CSegmentTable::ColumnID::N_HEIGHT));
				segment->SetLength(allRecords->GetColIntValue((int)CSegmentTable::ColumnID::N_LENGTH));
				segments.push_back(segment);
				mapSegments.insert(std::pair <std::vector <int>, COTSSegmentsList>(fldvec, segments));
				//mapSegments[fldvec] = segments;
			}
			else
			{
				//auto pairseg = *itr;
				COTSSegmentsList& segments = itr->second;
				COTSSegmentPtr segment = COTSSegmentPtr(new COTSSegment());
				segment->SetStart(allRecords->GetColIntValue((int)CSegmentTable::ColumnID::N_START));
				segment->SetHeight(allRecords->GetColIntValue((int)CSegmentTable::ColumnID::N_HEIGHT));
				segment->SetLength(allRecords->GetColIntValue((int)CSegmentTable::ColumnID::N_LENGTH));
				segments.push_back(segment);
				
			}
			allRecords->NextRow();
		} ;
		allRecords->Close();
		return true;
	}


	BOOL CSegmentDB::Init(const BOOL a_bClean /*= FALSE*/)
	{
		return myDB->Init(a_bClean);
	}

	BOOL CSegmentDB::CreateTable(const BOOL a_bForce /*= FALSE*/)
	{
		return myDB->CreateTable(a_bForce);
	}

	BOOL CSegmentDB::DeleteTable()
	{
		return myDB->DeleteTable();
	}

	BOOL CSegmentDB::RemoveAllRows()
	{
		return myDB->RemoveAllRows();
	}

	BOOL CSegmentDB::IsDBExist()
	{
		return myDB->IsDBExist();
	}

	OTSSQLITE::CDBStoreBasePtr CSegmentDB::GetDatastore()
	{
		return myDB->GetDatastore();
	}

	OTSSQLITE::CDBQueryBasePtr CSegmentDB::GetTableQuery(LPCTSTR a_sOrderColumnName /*= nullptr*/)
	{
		return myDB->GetTableQuery(a_sOrderColumnName);
	}

}