OTS/OTSIncAMeasureApp/0-OTSModel/Measure/DBDataTransition/SegmentDB.cs

using OTSCLRINTERFACE;
using OTSDataType;
using OTSModelSharp.DTLBase;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OTSModelSharp
{
  public  class CSegmentDB: CSQLiteDB
  {
      
        // particle list
       List< COTSParticleClr> m_listParticle;


     public CSegmentDB(IDBStoreBase _conStr, CSQLiteTable _table):base(_conStr,_table)	
	{
		
    }

        public COTSFeatureClr GetFeatureById( long a_nXrayId,  long a_nFieldId,  long a_nSegmentSize)
	{

        COTSFeatureClr pFeatureNew = null;

		if (m_listParticle.Count!=0)
		{
			foreach (var pParticle in m_listParticle)
			{
			COTSFeatureClr pFeature = pParticle.GetFeature();
            if (pParticle.GetAnalysisId() == a_nXrayId && pParticle.GetFieldId() == a_nFieldId && (int) pFeature.GetSegmentsList().Count == a_nSegmentSize)
			{ 	
            pFeatureNew = new COTSFeatureClr(pFeature);
    }
    }
		}
		else
		{
			// read element list
			for (int i = 0; i<a_nSegmentSize; i++)
			{
				var tableQuery = GetQueryById(a_nXrayId, a_nFieldId, i, a_nSegmentSize); //CElementChemistryDB


                COTSParticleClr pParticle = ReadParticleInfo(tableQuery);


                COTSFeatureClr pFeature = pParticle.GetFeature();

                pFeatureNew = new COTSFeatureClr(pFeature);
			}

		}

		return pFeatureNew;
	}

        public  List< COTSParticleClr>  GetParticleInfoList( bool a_bForce/* = FALSE*/)
	{
		if (a_bForce)
		{
        m_listParticle.Clear();
		}

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

		return m_listParticle;
	}

        public  COTSParticleClr ReadParticleInfo(DataTable a_query)
        {
            int nCol;
            COTSParticleClr pParticle=new COTSParticleClr();

            int nXrayIdNow;
            int nFieldIdNow;
           // CSegmentTable类改完再改此处
            nCol = (int)CSegmentTable.ColumnID.N_XRAY_INDEX - (int)CSegmentTable.ColumnID.MIN;
            nXrayIdNow = (int)a_query.Rows[(int)CSegmentTable.ColumnID.N_XRAY_INDEX][0];
            pParticle.SetAnalysisId(nXrayIdNow);

            nCol = (int)CSegmentTable.ColumnID.N_FIELD_ID - (int)CSegmentTable.ColumnID.MIN;
            nFieldIdNow = (int)a_query.Rows[(int)CSegmentTable.ColumnID.N_FIELD_ID][0];
            pParticle.SetFieldId(nFieldIdNow);

            nCol = (int)CSegmentTable.ColumnID.N_SEGMENT_ID - (int)CSegmentTable.ColumnID.MIN;
            int nSegmentIndex = (int)a_query.Rows[(int)CSegmentTable.ColumnID.N_SEGMENT_ID][0];

            nCol = (int)CSegmentTable.ColumnID.N_SEGMENT_TOTAL - (int)CSegmentTable.ColumnID.MIN;
            int nSegmentTotal = (int)a_query.Rows[(int)CSegmentTable.ColumnID.N_SEGMENT_TOTAL][0];
            //pParticle->SetElementNum(nElementTotal);

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

            COTSSegmentClr pSegment = new COTSSegmentClr();

            nCol = (int)CSegmentTable.ColumnID.N_START - (int)CSegmentTable.ColumnID.MIN;
            int nStart = (int)a_query.Rows[(int)CSegmentTable.ColumnID.N_START][0];
            pSegment.SetStart(nStart);

            nCol = (int)CSegmentTable.ColumnID.N_HEIGHT - (int)CSegmentTable.ColumnID.MIN;
            int nHeight = (int)a_query.Rows[(int)CSegmentTable.ColumnID.N_HEIGHT][0];
            pSegment.SetHeight(nHeight);

            nCol = (int)CSegmentTable.ColumnID.N_LENGTH - (int)CSegmentTable.ColumnID.MIN;
            int nLength = (int)a_query.Rows[(int)CSegmentTable.ColumnID.N_LENGTH][0];
            pSegment.SetLength(nLength);

            List<COTSSegmentClr> listSegment = new List<COTSSegmentClr>();
            listSegment.Add(pSegment);

            a_query.NewRow();

            int nXrayNew, nFieldIdNew;

            int nRowId = 0;

            
            for (int i = 0; i < a_query.Rows.Count; i++)
            {
                nCol = (int)CSegmentTable.ColumnID.N_XRAY_INDEX - (int)CSegmentTable.ColumnID.MIN;
                nXrayNew = (int)a_query.Rows[(int)CSegmentTable.ColumnID.N_XRAY_INDEX][0];

                nCol = (int)CSegmentTable.ColumnID.N_FIELD_ID - (int)CSegmentTable.ColumnID.MIN;
                nFieldIdNew = (int)a_query.Rows[(int)CSegmentTable.ColumnID.N_FIELD_ID][0];

                if (nXrayNew == nXrayIdNow && nFieldIdNew == nFieldIdNow)
                {
                    nCol = (int)CSegmentTable.ColumnID.N_SEGMENT_ID - (int)CSegmentTable.ColumnID.MIN;
                    int nSegmentIndexs = (int)a_query.Rows[(int)CSegmentTable.ColumnID.N_SEGMENT_ID][0];

                    nCol = (int)CSegmentTable.ColumnID.N_SEGMENT_TOTAL - (int)CSegmentTable.ColumnID.MIN;
                    int nSegmentTotals = (int)a_query.Rows[(int)CSegmentTable.ColumnID.N_SEGMENT_TOTAL][0];

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

                    nCol = (int)CSegmentTable.ColumnID.N_START - (int)CSegmentTable.ColumnID.MIN;
                    int Start = (int)a_query.Rows[(int)CSegmentTable.ColumnID.N_START][0];
                    pSegment.SetStart(nStart);

                    nCol = (int)CSegmentTable.ColumnID.N_HEIGHT - (int)CSegmentTable.ColumnID.MIN;
                    int Height = (int)a_query.Rows[(int)CSegmentTable.ColumnID.N_HEIGHT][0];
                    pSegment.SetHeight(nHeight);

                    nCol = (int)CSegmentTable.ColumnID.N_LENGTH - (int)CSegmentTable.ColumnID.MIN;
                    int Length = (int)a_query.Rows[(int)CSegmentTable.ColumnID.N_LENGTH][0];
                    pSegment.SetLength(nLength);

                    listSegment.Add(pSegment);

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

                a_query.NewRow();
                nRowId++;
            }

            COTSFeatureClr pFeature = new COTSFeatureClr();
            pFeature.SetSegmentsList(listSegment,true);

            pParticle.SetFeature(pFeature);

            return pParticle;
        }

    public  bool SaveFeature( List<COTSParticleClr> a_ParticleList)
	{
            //System.Data.DataTable query;
            var tableInfoPtr = GetTableInfo();   
            var datastorePtr = GetDatastore();
	        String sInsertFormat =tableInfoPtr. GetInsertCommandFormatString(true);//SQLiteTable
            String sSQLCommand = "";
		foreach (var pParticle in a_ParticleList)
		{			
		COTSFeatureClr pFeature = pParticle.GetFeature();
	    List< COTSSegmentClr> listSegments = pFeature.GetSegmentsList();
            int nSize = (int)listSegments.Count;
            int nSegmentIndex = 0;
			foreach (var pSegment in listSegments)
			{
			sSQLCommand=	String.Format(sInsertFormat,
                    pParticle.GetAnalysisId(),
					pParticle.GetFieldId(),
					nSegmentIndex,
					nSize,
					pSegment.GetStart(),
					pSegment.GetHeight(),
					pSegment.GetLength(),
					pParticle.GetTagId());
                  
                    datastorePtr.RunCommand(sSQLCommand);
    
				nSegmentIndex++;
			}
		}
            return true;
	}

     public  bool SaveFeature(COTSParticleClr a_pParticle)
        {
            //System.Data.DataTable query;
            var tableInfoPtr = GetTableInfo();
           
            var datastorePtr = GetDatastore();
            
            String sInsertFormat = tableInfoPtr.GetInsertCommandFormatString(true);//CSQLiteTable修改完再修改此处
            String sSQLCommand = "";

            COTSFeatureClr pFeature = a_pParticle.GetFeature();
            List< COTSSegmentClr> listSegments = pFeature.GetSegmentsList();
           
            int nSize = (int)listSegments.Count;
            int nSegmentIndex = 0;
            foreach (var pSegment in listSegments)
            {
                sSQLCommand=String.Format(sInsertFormat,
                    a_pParticle.GetAnalysisId(),
                    a_pParticle.GetFieldId(),
                    nSegmentIndex,
                    nSize,
                    pSegment.GetStart(),
                    pSegment.GetHeight(),
                    pSegment.GetLength(),
                    a_pParticle.GetTagId());

               
                 datastorePtr.RunCommand(sSQLCommand);
                

                nSegmentIndex++;
            }

            return true;
        }
        public List<KeyValuePair<string,SQLiteParameter[]>> GetSavingFeatureCmd(COTSParticleClr a_pParticle)
        {
            var tableInfoPtr = GetTableInfo();

            var datastorePtr = GetDatastore();

            var sInsertFormat = tableInfoPtr.GetInsertCommand(true);//CSQLiteTable修改完再修改此处
            //String sSQLCommand = "";

            COTSFeatureClr pFeature = a_pParticle.GetFeature();
            List<COTSSegmentClr> listSegments = pFeature.GetSegmentsList();

            int nSize = (int)listSegments.Count;
            int nSegmentIndex = 0;
            List<KeyValuePair<string, SQLiteParameter[]>> cmds = new List<KeyValuePair<string, SQLiteParameter[]>>();

            var parasTemplate = (SQLiteParameter[])sInsertFormat.Value;
            foreach (var pSegment in listSegments)
            {
                var paras = new SQLiteParameter[8];
                for (int j = 0; j < 8; j++)
                {
                    paras[j] = new SQLiteParameter(parasTemplate[j].ParameterName);
                   
                }
               
                paras[0].Value = a_pParticle.GetAnalysisId();
               
                paras[1].Value = a_pParticle.GetFieldId();
                paras[2].Value = nSegmentIndex;
                paras[3].Value = nSize;
                paras[4].Value = pSegment.GetStart();
                paras[5].Value = pSegment.GetHeight();
                paras[6].Value = pSegment.GetLength();
                paras[7].Value=  a_pParticle.GetTagId();

            


                nSegmentIndex++;
                cmds.Add(new KeyValuePair<string, SQLiteParameter[]>(sInsertFormat.Key, paras));
            }

            return cmds;
        }


            public  bool DeleteFeatureById( long a_nFieldId,  long a_nXrayId)
	{
            System.Data.DataTable query;
            if (m_listParticle.Count!=0)
		{

               
                for (int itr = 0; itr < m_listParticle.Count; itr++)
                {
                    if (m_listParticle[itr].GetTypeName() ==a_nFieldId.ToString())
                    {
                        COTSParticleClr particle = new COTSParticleClr();
                        particle = m_listParticle[itr];
                        break;
                    }
                }


            }

       var tableInfoPtr = GetTableInfo();
		var datastorePtr = GetDatastore();

            String sTableName = tableInfoPtr.GetTableName();
		if (!IsTableExists(""))
		{

                return true;
		}
        //CSQLiteTable 修改完再修改此处
		var sXrayIdColumnName = tableInfoPtr.GetColumnName((int)CSegmentTable.ColumnID.N_XRAY_INDEX - (int)CSegmentTable.ColumnID.MIN);
        var sFieldIdColumnName = tableInfoPtr.GetColumnName((int)CSegmentTable.ColumnID.N_FIELD_ID - (int)CSegmentTable.ColumnID.MIN);

            String sSQLCommand = "";
            String.Format("DELETE FROM \'%s\' WHERE %s = %d AND %s = %d AND %s = %d AND %s = %d;", (String)tableInfoPtr.GetTableName(), sXrayIdColumnName,
            a_nXrayId, sFieldIdColumnName, a_nFieldId);
            //var helper = new SQLiteHelper(datastorePtr);
            datastorePtr.RunCommand(sSQLCommand);
           
            return true;
	}

       

        public  bool IsTableExists(String a_sTableName)
        {
           
            return tableExists(a_sTableName);//OTSSQLiteDll,SQLiteStore中的GetDBPtr()方法修改完再修改此处

        }

       public bool tableExists(string szTable)
        {
            String sSQL = "";
            string.Format("select count(*) from sqlite_master where type='table' and name='%s'", szTable);
            int nRet = Convert.ToInt32(sSQL);
            return (nRet > 0);
        }

      

        public  bool GetAllSegmentsRecord(Dictionary<string, List<COTSSegmentClr>> mapSegments)
        {                  
            var allRecords = this.GetQueryOfAllRecord();
            for (int i = 0; i < allRecords.Rows.Count; i++)
            {
                int curFldId = Convert.ToInt32(allRecords.Rows[i][(int)CSegmentTable.ColumnID.N_FIELD_ID]);
                int curParticleId = Convert.ToInt32(allRecords.Rows[i][(int)CSegmentTable.ColumnID.N_PARTICLE_ID]);
                string fldvec = "";
                fldvec+=curFldId.ToString();
                fldvec += "_";
                fldvec += curParticleId.ToString();
            //var itr = mapSegments.ContainsKey(fldvec);                   
            if (mapSegments.ContainsKey(fldvec))
                {
                    List<COTSSegmentClr> segments = mapSegments[fldvec];
                    COTSSegmentClr segment = new COTSSegmentClr();
                    segment.SetStart(Convert.ToInt32(allRecords.Rows[i][(int)CSegmentTable.ColumnID.N_START]));
                    segment.SetHeight(Convert.ToInt32(allRecords.Rows[i][(int)CSegmentTable.ColumnID.N_HEIGHT]));
                    segment.SetLength(Convert.ToInt32(allRecords.Rows[i][(int)CSegmentTable.ColumnID.N_LENGTH]));
                    //segment.UpDownConection(segment);
                    segments.Add(segment);                  
                                 
                }
                else
                {
                    //auto pairseg = *itr;
                    List<COTSSegmentClr> segments = new List<COTSSegmentClr>();
                    COTSSegmentClr segment = new COTSSegmentClr();
                    segment.SetStart(Convert.ToInt32(allRecords.Rows[i][(int)CSegmentTable.ColumnID.N_START]));
                    segment.SetHeight(Convert.ToInt32(allRecords.Rows[i][(int)CSegmentTable.ColumnID.N_HEIGHT]));
                    segment.SetLength(Convert.ToInt32(allRecords.Rows[i][(int)CSegmentTable.ColumnID.N_LENGTH]));
                    segments.Add(segment);
                    mapSegments.Add(fldvec, segments);
                }


                   
            }
            //allRecords.Dispose();                
            return true;
        }

       public bool ReadParticleInfoList()
       {
            var tableInfoPtr = GetTableInfo();
           
            var query =GetTableQueryForDataTable (tableInfoPtr.GetTableName());//CElementChemistryDB

            m_listParticle = ReadParticleInfoList(query);

            return true;
       }

        public List< COTSParticleClr> ReadParticleInfoList(DataTable a_query)
        {
            List<COTSParticleClr> listParticle = new List<COTSParticleClr>();

            int nRowId = 0;
            int nWrongItems = 0;

            DataTable dataTable = new DataTable();
           foreach(var r in a_query.Rows)
            {
                var ParticleInfo = ReadParticleInfo(dataTable);  //current x-ray point		

                if (ParticleInfo != null)
                {
                   
                    nWrongItems++;
                }
                else
                {
                    if (listParticle.Count!=0)
                    {
                        int nXrayId = ParticleInfo.GetAnalysisId();
                        int nFieldId = ParticleInfo.GetFieldId();

                        COTSFeatureClr pFeature = ParticleInfo.GetFeature();
                     
                       List< COTSSegmentClr> listSegmentNew = pFeature.GetSegmentsList();

                        int nIndex = 0;

                        foreach (var pParticle in listParticle)
                        {

                            listParticle.Remove(pParticle);

                            if ((pParticle.GetAnalysisId() == nXrayId)
                                && (pParticle.GetFieldId() == nFieldId))
                            {
                                COTSFeatureClr apFeature = pParticle.GetFeature();
                                
                              List < COTSSegmentClr> listSegmentOld = pFeature.GetSegmentsList();

                                foreach (var pSegment in listSegmentNew)
                                {
                                    listSegmentOld.Add(new COTSSegmentClr(pSegment));
                                }

                                pFeature.SetSegmentsList(listSegmentOld,true);

                                ParticleInfo.SetFeature(pFeature);

                                break;
                            }
                            nIndex++;
                        }

                    }

                    listParticle.Add(ParticleInfo);
                }

               
            }

            return listParticle;
        }

        public DataTable GetQueryById( long a_nXrayId,  long a_nFieldId,  long a_nSegmentId,  long a_nSegmentNum)
	{

        DataTable query;
        var datastorePtr = GetDatastore();
            //var helper = new SQLiteHelper(datastorePtr);

        var tableInfoPtr = GetTableInfo();

            //没有SegmentTable类，有了再修改此处 
            // String sXrayIdColumnName = tableInfoPtr.columns((int)CSegmentTable.ColumnID.N_XRAY_INDEX - (int)CSegmentTable.ColumnID.MIN);
            var sXrayIdColumnName = tableInfoPtr.GetColumnName((int)CSegmentTable.ColumnID.N_XRAY_INDEX - (int)CSegmentTable.ColumnID.MIN);
            //  String sXrayIdColumnName = tableInfoPtr.GetColumnName((int)CSegmentTable.ColumnID.N_XRAY_INDEX - (int)CSegmentTable.ColumnID.MIN);
            var sFieldIdColumnName = tableInfoPtr.GetColumnName((int)CSegmentTable.ColumnID.N_FIELD_ID - (int)CSegmentTable.ColumnID.MIN);
        String sElementIdColumnName = tableInfoPtr.GetColumnName((int)CSegmentTable.ColumnID.N_SEGMENT_ID - (int)CSegmentTable.ColumnID.MIN);
        String sElementNumColumnName = tableInfoPtr.GetColumnName((int)CSegmentTable.ColumnID.N_SEGMENT_TOTAL - (int)CSegmentTable.ColumnID.MIN);

           // String sSQLCommand;
            String sSQLCommand=String.Format("SELECT * FROM \'{0}\' WHERE {1} = {2} AND {3} ={4} AND {5} ={6} AND {7} = {8};",(String)tableInfoPtr.GetTableName(),
            (String)sFieldIdColumnName,a_nFieldId,(String)sXrayIdColumnName,a_nXrayId,a_nSegmentId, (String)sElementNumColumnName,
            a_nSegmentNum);

		    query = datastorePtr.QueryByCmdForDataTable(sSQLCommand);//SQLiteStote在OTSSQLiteDll中修改完再修改此处

 

		return query;
	}

        public  DataTable GetQueryOfAllRecord()//CDBQueryBase在OTSClassifyEngine中修改完再更改此处
        {

            DataTable query;
            var datastorestr = GetDatastore();
            
            var tableInfoPtr = GetTableInfo();


            String sSQLCommand=String.Format("SELECT * FROM \'{0}\';", tableInfoPtr.GetTableName());
            //sSQLCommand= tableInfoPtr.GetTableName();
            //var helper = new SQLiteHelper(datastorestr);
            query = datastorestr.QueryByCmdForDataTable(sSQLCommand);//SQLiteStote在OTSSQLiteDll中修改完再修改此处
            
            // do the table related valid checking
        
            return query;
        }

    }
}