OTS/OTSIncAMeasureApp/0-OTSModel/Measure/DBDataTransition/ElementChemistryDB.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.DTLBase
{
    public class CElementChemistryDB : CSQLiteDB
    {
        List<CPosXrayClr> m_listPosXrayInfo = new List<CPosXrayClr>();
        public CElementChemistryDB(IDBStoreBase _conStr, IDBTableBase _table):base(_conStr,_table) 		
	{
		

    }

 

      public System.Data.DataTable GetQueryById( long a_nXrayId, long a_nFieldId,  long a_nElementId,  long a_nElementSize)
	  {

        

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

        var sXrayIdColumnName = tableInfoPtr.GetColumnName((int)CElementChemistryTable.ColumnID.N_XRAY_INDEX - (int)CElementChemistryTable.ColumnID.MIN);
        var sFieldIdColumnName = tableInfoPtr.GetColumnName((int)CElementChemistryTable.ColumnID.N_FIELD_ID - (int)CElementChemistryTable.ColumnID.MIN);
        var sElementIdColumnName = tableInfoPtr.GetColumnName((int)CElementChemistryTable.ColumnID.N_ELEMENT_ID - (int)CElementChemistryTable.ColumnID.MIN);
        var sElementNumColumnName = tableInfoPtr.GetColumnName((int)CElementChemistryTable.ColumnID.N_ELEMENT_TOTAL - (int)CElementChemistryTable.ColumnID.MIN);


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

      

        var q=datastorePtr.QueryByCmdForDataTable(sSQLCommand);
           
        return q;
	  }

    

     public   List< CPosXrayClr> GetXrayInfoList( bool a_bForce/* = FALSE*/)
	{
		if (a_bForce)
		{
            m_listPosXrayInfo.Clear();
		}

		if (m_listPosXrayInfo.Count == 0)
		{
			ReadXrayPointInfoList();
        }

		return m_listPosXrayInfo;
	}

       public  bool ReadXrayPointInfoList()
        {
            var tableInfoPtr = GetTableInfo();
            
            var query = GetTableQueryForDataTable("");
           
            m_listPosXrayInfo = ReadXrayPointInfoList(query);

            return true;
        }

     
      public  bool SaveElementChemistriesList(List<CPosXrayClr> a_xrayPointList)
	{
		
          var tableInfoPtr = GetTableInfo();
   
          var datastorePtr = GetDatastore();

            String sInsertFormat = tableInfoPtr.GetInsertCommandFormatString();
          String sSQLCommand = "";

		foreach (var xrayPointInfo in a_xrayPointList)
		{
			List<CElementChemistryClr> listElemnentChemistries = xrayPointInfo.GetElementQuantifyData();

            int nSize = (int)listElemnentChemistries.Count;
            int nElementIndex = 0;
			foreach (var pElementChemistry in listElemnentChemistries)
			{
                    sSQLCommand= String.Format(sInsertFormat,
                    xrayPointInfo.GetIndex(),
					xrayPointInfo.GetScanFieldId(),
					nElementIndex,
					nSize,
					pElementChemistry.GetName(),
					pElementChemistry.GetPercentage());
                    //var helper = new SQLiteHelper(datastorePtr);         
                    if (datastorePtr.RunCommand(sSQLCommand) != true)
				   {



                        return false;
				}

				nElementIndex++;
			}

		}

            return true;
	}

      public  bool SaveElementChemistriesList( CPosXrayClr a_pxrayPoint)
	{
		
       var tableInfoPtr = GetTableInfo();
    
       var datastorePtr = GetDatastore();

            String sInsertFormat = tableInfoPtr.GetInsertCommandFormatString();
        String sSQLCommand = "";

       List<CElementChemistryClr >listElemnentChemistries = a_pxrayPoint.GetElementQuantifyData();

        int nSize = (int)listElemnentChemistries.Count;
        int nElementIndex = 0;
		foreach (var pElementChemistry in listElemnentChemistries)
		{
                sSQLCommand=String.Format(sInsertFormat,
                a_pxrayPoint.GetIndex(),
				a_pxrayPoint.GetScanFieldId(),
				nElementIndex,
				nSize,
				pElementChemistry.GetName(),
				pElementChemistry.GetPercentage(),
				pElementChemistry.GetMolarPercentage());
                    
                if (datastorePtr.RunCommand(sSQLCommand) != true)
			    {

                    return false;
			    }

			nElementIndex++;
		}

            return true;
	}
        public List<KeyValuePair<string,SQLiteParameter[]>> GetSavingElementChemistriesCmds(CPosXrayClr a_pxrayPoint)
        {
            List<KeyValuePair<string, SQLiteParameter[]>> cmds = new List<KeyValuePair<string, SQLiteParameter[]>>();
            var tableInfoPtr = GetTableInfo();

          

            var sInsertFormat = tableInfoPtr.GetInsertCommand();
            var parasTem = sInsertFormat.Value;

            List<CElementChemistryClr> listElemnentChemistries = a_pxrayPoint.GetElementQuantifyData();

            int nSize = (int)listElemnentChemistries.Count;
            int nElementIndex = 0;
            foreach (var pElementChemistry in listElemnentChemistries)
            {
                var paras = new SQLiteParameter[6];
                for (int j = 0; j < 6; j++)
                {
                    paras[j] = new SQLiteParameter(parasTem[j].ParameterName);
                }

                paras[0].Value = a_pxrayPoint.GetIndex();
                paras[1].Value = a_pxrayPoint.GetScanFieldId();
                paras[2].Value = nElementIndex;
                paras[3].Value = nSize;
                paras[4].Value = pElementChemistry.GetName();
                paras[5].Value = pElementChemistry.GetPercentage();
             

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

            return cmds;
        }

        public bool DeleteElementChemistryById(long a_nFieldId, long a_nXrayId)
        {            
            if (m_listPosXrayInfo.Count != 0)
            {
                for (int itr = 0; itr < m_listPosXrayInfo.Count; itr++)
                {
                    if (m_listPosXrayInfo[itr].ToString() == a_nFieldId.ToString())
                    {
                        CPosXrayClr posXrayInfo = new CPosXrayClr();
                        posXrayInfo = m_listPosXrayInfo[itr];
                        break;
                    }
                }
            }
            DataTable query;
            var tableInfoPtr = GetTableInfo();
            var datastorePtr = GetDatastore();
            String sTableName = tableInfoPtr.GetTableName();
            if (IsTableExists(sTableName))
            {
                

           

            var sXrayIdColumnName = tableInfoPtr.GetColumnName((int)CElementChemistryTable.ColumnID.N_XRAY_INDEX - (int)CElementChemistryTable.ColumnID.MIN);
            var sFieldIdColumnName = tableInfoPtr.GetColumnName((int)CElementChemistryTable.ColumnID.N_FIELD_ID - (int)CElementChemistryTable.ColumnID.MIN);

            String sSQLCommand = 
            String.Format("DELETE FROM \'{0}\' WHERE {1} = {2} AND {3} ={4};",
			sTableName,
			sXrayIdColumnName,
            a_nXrayId,
			sFieldIdColumnName,
			a_nFieldId);

            //var helper = new SQLiteHelper(datastorePtr);

             datastorePtr.RunCommand(sSQLCommand);
            }
            return true;

    }

        public bool IsTableExists(String a_sTableName)
        {

            return IsDBExist();

        }

       public List<CPosXrayClr> ReadXrayPointInfoList(DataTable a_query)
        {
            List<CPosXrayClr> xrayList = new List<CPosXrayClr>();
            Dictionary<string, CPosXrayClr> mapXrayInfo = new Dictionary<string, CPosXrayClr>();
            foreach (DataRow row in a_query.Rows)
            {
               int nCol = (int)CElementChemistryTable.ColumnID.N_FIELD_ID- (int)CElementChemistryTable.ColumnID.MIN;
                int curFldId = Convert.ToInt32(row[nCol]);
                nCol = (int)CElementChemistryTable.ColumnID.N_XRAY_INDEX - (int)CElementChemistryTable.ColumnID.MIN;
                int xrayId = Convert.ToInt32(row[nCol]);
                string fldvec = "";
                fldvec+=curFldId.ToString();
                fldvec += "_";
                fldvec += xrayId.ToString();
               
                nCol = (int)CElementChemistryTable.ColumnID.S_NAME - (int)CElementChemistryTable.ColumnID.MIN;
                string cheName = Convert.ToString(row[nCol]);
                nCol = (int)CElementChemistryTable.ColumnID.F_PERCENTAGE - (int)CElementChemistryTable.ColumnID.MIN;
                double percentage = Convert.ToDouble(row[nCol]);
                CElementChemistryClr che = new CElementChemistryClr(cheName,percentage);

                if (!mapXrayInfo.ContainsKey(fldvec))
                {
                    CPosXrayClr xray = new CPosXrayClr();
                    xray.AddQuantifyElement(che);

                    mapXrayInfo.Add(fldvec, xray);
                }
                else 
                {
                    CPosXrayClr xray = mapXrayInfo[fldvec];
                    xray.AddQuantifyElement(che);
                }


            }
            foreach (var keyvalue in mapXrayInfo)
            {
                xrayList.Add(keyvalue.Value);


            }
            return xrayList;
            
        }
      public  bool GetAllMapedXrayInfo(ref Dictionary<string, CPosXrayClr> mapXrayInfo)
        {
            DataTable a_query = GetTableQueryForDataTable();
            //Dictionary<string, CPosXrayClr> mapXrayInfo = new Dictionary<string, CPosXrayClr>();
            foreach (DataRow row in a_query.Rows)
            {
                int nCol = (int)CElementChemistryTable.ColumnID.N_FIELD_ID - (int)CElementChemistryTable.ColumnID.MIN;
                int curFldId = Convert.ToInt32(row[nCol]);
                nCol = (int)CElementChemistryTable.ColumnID.N_XRAY_INDEX - (int)CElementChemistryTable.ColumnID.MIN;
                int xrayId = Convert.ToInt32(row[nCol]);
                string fldvec = "";
                fldvec += curFldId.ToString();
                fldvec += "_";
                fldvec += xrayId.ToString();

                nCol = (int)CElementChemistryTable.ColumnID.S_NAME - (int)CElementChemistryTable.ColumnID.MIN;
                string cheName = Convert.ToString(row[nCol]);
                nCol = (int)CElementChemistryTable.ColumnID.F_PERCENTAGE - (int)CElementChemistryTable.ColumnID.MIN;
                double percentage = Convert.ToDouble(row[nCol]);
                CElementChemistryClr che = new CElementChemistryClr(cheName, percentage);

                if (!mapXrayInfo.ContainsKey(fldvec))
                {
                    CPosXrayClr xray = new CPosXrayClr();
                    xray.AddQuantifyElement(che);

                    mapXrayInfo.Add(fldvec, xray);
                }
                else
                {
                    CPosXrayClr xray = mapXrayInfo[fldvec];
                    xray.AddQuantifyElement(che);
                }


            }
            return true;
        }


    }
}