OTS/OTSIncAMeasureApp/1-OTSMeasure/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_Batch(List<CPosXrayClr> a_xrayPointList)
        {
            List<KeyValuePair<string, SQLiteParameter[]>> list = new List<KeyValuePair<string, SQLiteParameter[]>>();
            foreach (var xrayPointInfo in a_xrayPointList)
            {
                List<CElementChemistryClr> listElemnentChemistries = xrayPointInfo.GetElementQuantifyData();
                int nSize = (int)listElemnentChemistries.Count;
                int nElementIndex = 0;
                foreach (var pElementChemistry in listElemnentChemistries)
                {
                    //INSERT INTO ElementChemistry(XRayId, FieldId, ElementId, ElementNum, Name, Percentage) VALUES({ 0:G},{ 1:G},{ 2:G},{ 3:G},"{4:G}",{ 5:G})
                    string sSQLCommand = "INSERT INTO ElementChemistry(XRayId, FieldId, ElementId, ElementNum, Name, Percentage) VALUES(@XRayId,@FieldId,@ElementId,@ElementNum,@Name,@Percentage)";
                    var paras = new SQLiteParameter[6];
                    paras[0] = new SQLiteParameter("XRayId");
                    paras[0].Value = xrayPointInfo.GetIndex();
                    paras[1] = new SQLiteParameter("FieldId");
                    paras[1].Value = xrayPointInfo.GetScanFieldId();
                    paras[2] = new SQLiteParameter("ElementId");
                    paras[2].Value = nElementIndex;
                    paras[3] = new SQLiteParameter("ElementNum");
                    paras[3].Value = nSize;
                    paras[4] = new SQLiteParameter("Name");
                    paras[4].Value = pElementChemistry.GetName();
                    paras[5] = new SQLiteParameter("Percentage");
                    paras[5].Value = pElementChemistry.GetPercentage();
                    list.Add(new KeyValuePair<string, SQLiteParameter[]>(sSQLCommand, paras));
                }
            }
            var datastorePtr = GetDatastore();
            try
            {
                datastorePtr.ExecuteNonQueryBatch(ref list);
                return true;
            }
            catch (Exception e)
            {
                NLog.LogManager.GetCurrentClassLogger().Error(e.Message);
                return false;
            }
        }
        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;
        }


    }
}