OTS/OTSIncAReportApp/3-ServiceCenter/DataOperation/DataAccess/ParticleData.cs

using OTSCommon.DBOperate.Model;
using OTSIncAReportGraph.Controls;
using OTSPeriodicTable;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using static OTSIncAReportApp.OTSReport_Export;

namespace OTSIncAReportApp.DataOperation.DataAccess
{
    public class ParticleData
    {
        private SqlHelper dbHelper;

        public ParticleData(string path)
        {
            dbHelper = new SqlHelper("data source='" + path + "\\FIELD_FILES\\Inclusion.db'");
        }
        /// <summary>
        /// 获取SegmentList
        /// </summary>
        /// <param name="model">Feature</param>
        /// <returns></returns>
        public List<Particle> GetParticleList(Particle model)
        {
            //存放查询数据的数据表
            SQLiteParameter[] Parameter = new SQLiteParameter[1]
            {
                new SQLiteParameter("@FieldId", model.FieldId)
            };
            string sql = "select * from IncAData where Fieldid=@FieldId";
            DataTable DT = dbHelper.ExecuteDataTable(sql, Parameter);
            var result = new List<Particle>();
            foreach (DataRow dr in DT.Rows)
            {
                Particle item = new Particle() { };
                result.Add(item);
            }
            return result;

        }
        /// <summary>
        /// 获取ParticleListBy
        /// </summary>
        /// <param name="model">Feature</param>
        /// <returns></returns>
        public List<Particle> GetParticleListByCon(string condition, string max, string min, int display)
        {
            string sqlp = "select a.* from IncAData a ";
            string where = " where 1=1 ";
            if (display == 1)
            {
                where = where + " and a.XrayId >-1 ";
            }
            if (condition != "")
            {
                where = where + " and a." + condition + ">" + min + " and a." + condition + "<" + max;
            }
            sqlp = sqlp + where;
            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            List<Particle> listp = dbHelper.TableToList<Particle>(DT);
            return listp;

        }


        /// <summary>
        /// 获取ParticleList
        /// </summary>
        /// <param name="model">Feature</param>
        /// <returns></returns>
        public DataTable GetParticleListAndEm()
        {
            string sqlp = @"select *,
                            (select group_concat(name||'-'||Percentage,';')  
                            from ElementChemistry where XRayId	=INcAData.XRayId		and fieldid=INcAData.fieldid	) as Element
                            from 	INcAData where xrayid>-1";

            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            return DT;
        }

        
        
        
        
        /// <summary>
        /// 获取ParticleList
        /// </summary>
        /// <param name="model">Feature</param>
        /// <returns></returns>
        public Particle GetMergedParticleInfo(int fieldid, int particleid, out uint[] Analysis_xray)
        {
            string sqlp = @"select *,
                            (select group_concat(name||'-'||Percentage,';')  
                            from ElementChemistry where XRayId	=IncAData.XRayId		and fieldid=IncAData.fieldid	) as Element
                            from 	IncAData  where FieldId=" + fieldid.ToString() + " and ParticleId="+ particleid.ToString();

            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            List<Particle> listp = dbHelper.TableToList<Particle>(DT);
            Particle pte = new Particle();
            Analysis_xray =new uint[2000];
            if (listp.Count > 0)
            {
                pte = listp[0];
                List<OTSCommon.DBOperate.Model.Element> ElementList = new List<OTSCommon.DBOperate.Model.Element>();
                string element = DT.Rows[0]["Element"].ToString();
                for (int i = 0; i < element.Split(';').Count(); i++)
                {
                    string elestr = element.Split(';')[i];
                    if (elestr != "")
                    {
                        OTSCommon.DBOperate.Model.Element ele = new OTSCommon.DBOperate.Model.Element() { Name = elestr.Split('-')[0], Percentage = Convert.ToDouble(elestr.Split('-')[1]) };
                        ElementList.Add(ele);
                    }
                }
                pte.ElementList = ElementList;
                string SubParticlesstr = DT.Rows[0]["SubParticles"].ToString();
                string[] SubParticlesstrG = SubParticlesstr.Split(',');
                foreach (string s in SubParticlesstrG)
                {
                    string fieldid1 = s.Split(':')[0];
                    string pid1 = s.Split(':')[1];
                    Particle particle1 = GetParticleXrayDataByFidAndPid(fieldid1, pid1);
                    byte[] bytes = particle1.XRayData;
                    for (int i = 0; i < 2000; i++)
                    {
                        Analysis_xray[i] = Analysis_xray[i]+ BitConverter.ToUInt32(bytes, i * 4);
                    }
                }
            }
            return pte;
        }

        /// <summary>
        /// 获取ParticleList
        /// </summary>
        /// <param name="model">Feature</param>
        /// <returns></returns>
        public DataTable GetParticleStatisticDataListByIncA(string condition)
        {
            string sqlp = @"select TypeId,TypeName,TypeColor,count(1) as con,sum(Area) as Area,avg(" + condition
                + ") as av,max(" + condition
                + ") as max ";
            sqlp = sqlp + "from IncAData where typeid !=-1 and typeid !=4 and SubParticles is not 'IsSubParticle' group by TypeId";

            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            return DT;
        }

        public DataTable GetParticleListForParticlSize(string condition, string fieldAndPartic)
        {
            string sqlp = @"select TypeId,TypeName,GroupId ,TypeColor,count(1) as con,avg(" + condition
                + ") as av,max(" + condition
                + ") as max ,GroupName";
            sqlp = sqlp + " from IncAData where typeid !=-1 and typeid !=4 and ParticleId !=-1 and SubParticles is not 'IsSubParticle'";
            if (fieldAndPartic != "")
            {
                sqlp = sqlp + " and '" + fieldAndPartic + "' like ('%,'||fieldid||'-'||particleid||',%')";
            }

            sqlp = sqlp + " group by TypeId ";
            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);

            if (condition == "area")
            {
                for (int i = 0; i < DT.Rows.Count; i++)
                {
                    DT.Rows[i]["max"] = Math.Sqrt((double)DT.Rows[i]["max"] / Math.PI) * 2;
                }
            }
            return DT;
        }






		/// <summary>
		/// 获取ParticleList
		/// </summary>
		/// <param name="model">Feature</param>
		/// <returns></returns>
		public DataTable GetParticleListForParticlSizeID(string condition, string fieldAndPartic)
		{
			string sqlp = @"select TypeId,TypeName,TypeColor,count(1) as con,avg(" + condition
				+ ") as av,max(" + condition
				+ ") as max ,GroupName ,GroupId,GroupColor";
			sqlp = sqlp + " from IncAData where typeid !=-1 and typeid !=4";
			if (fieldAndPartic != "")
			{
				sqlp = sqlp + " and '" + fieldAndPartic + "' like ('%,'||fieldid||'-'||particleid||',%')";
			}

			sqlp = sqlp + " group by TypeId ";

			DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);

			if (condition == "area")
			{
				for (int i = 0; i < DT.Rows.Count; i++)
				{
					DT.Rows[i]["max"] = Math.Sqrt((double)DT.Rows[i]["max"] / Math.PI) * 2;
				}
			}
			return DT;
		}

		/// <summary>
		/// 获取element含量
		/// </summary>
		/// <returns></returns>
		public DataTable GetElementForArea(string fieldAndPartic)
        {

            string sqlp = @"select e.name,sum(e.percentage*p.area) as earea from ElementChemistry e 
                inner join INcAData p on e.xrayid=p.xrayid and e.fieldid = p.fieldid from IncAData where typeid !=-1 and typeid !=4";
            if (fieldAndPartic != "")
            {
                sqlp = sqlp + " and '" + fieldAndPartic + "' like ('%,'||p.fieldid||'-'||p.particleid||',%')";
            }

            sqlp = sqlp + " group by e.name  order by sum(e.percentage*p.area) desc";
            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);

            return DT;
        }

        /// <summary>
        /// 获取element含量
        /// </summary>
        /// <returns></returns>
        public DataTable GetSmallElementForArea()
        {
            string sqlp = @"select e.name,  sum(e.percentage*i.area) as earea  from elementchemistry e inner join incadata i on e.xrayid = i.xrayid and e.fieldid = i.fieldid
                        group by e.name order by sum(e.percentage*i.area) desc";

            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            return DT;
        }

        /// <summary>
        /// 获取所有Particle
        /// </summary>
        /// <returns></returns>
        public DataTable GetParticleAll(string fieldAndPartic)
        {
            string sqlp = @"select * from INcAData where typeid !=-1 and typeid !=4 and SubParticles is not 'IsSubParticle'";
            if (fieldAndPartic != "")
            {
                sqlp = sqlp + " and '" + fieldAndPartic + "' like ('%,'||fieldid||'-'||particleid||',%')";
            }
            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            DT.Columns.Add("ECD",typeof(double));

            for (int i = 0; i < DT.Rows.Count; i++)
            {
                DT.Rows[i]["ECD"] = Math.Sqrt((double)DT.Rows[i]["Area"] / Math.PI) * 2;
            }

            DataTable data = DT.Clone();

			for (int i = 0; i < DT.Rows.Count; i++)
            {
                if (Convert.ToInt32(DT.Rows[i]["ParticleId"]) > -1)
                {
                    data.Rows.Add(DT.Rows[i].ItemArray);
				}

			}
            return data;
        }


		/// <summary>
		/// 获取所有Particle
		/// </summary>
		/// <returns></returns>
		public DataTable GetSpliceParticleAll(string fieldAndPartic)
		{
			string sqlp = @"select * from INcAData where   SubParticles =  'IsSubParticle'";
			if (fieldAndPartic != "")
			{
				sqlp = sqlp + " and '" + fieldAndPartic + "' like ('%,'||fieldid||'-'||particleid||',%')";
			}
			DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
			DT.Columns.Add("ECD", typeof(double));

			for (int i = 0; i < DT.Rows.Count; i++)
			{
				DT.Rows[i]["ECD"] = Math.Sqrt((double)DT.Rows[i]["Area"] / Math.PI) * 2;
			}

			DataTable data = DT.Clone();

			for (int i = 0; i < DT.Rows.Count; i++)
			{
				if (Convert.ToInt32(DT.Rows[i]["ParticleId"]) > -1)
				{
					data.Rows.Add(DT.Rows[i].ItemArray);
				}

			}
			return data;
		}


		public DataTable GetParticleAllForBig(string fieldAndPartic)
        {
            string sqlp = @"select * from INcAData where";
            if (fieldAndPartic != "")
            {
                sqlp = sqlp + " '" + fieldAndPartic + "' like ('%,'||fieldid||'-'||particleid||',%')";
            }
            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            DT.Columns.Add("ECD", typeof(double));

            for (int i = 0; i < DT.Rows.Count; i++)
            {
                DT.Rows[i]["ECD"] = Math.Sqrt((double)DT.Rows[i]["Area"] / Math.PI) * 2;
            }
            DataTable data = DT.Clone();
            for (int i = 0; i < DT.Rows.Count; i++)
            {
                if (Convert.ToInt32(DT.Rows[i]["ParticleId"]) > -1)
                {
                    data.Rows.Add(DT.Rows[i].ItemArray);
                }

            }
            return data;
        }


        public DataTable GetParticleAllforparticlelist(string fieldAndPartic)
        {
            string sqlp = @"select * from INcAData where typeid !=-1 and SubParticles is not 'IsSubParticle' ";
            if (fieldAndPartic != "")
            {
                sqlp = sqlp + " and '" + fieldAndPartic + "' like ('%,'||fieldid||'-'||particleid||',%')";
            }
            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            return DT;
        }
        public DataTable GetMergedParticle(string fieldAndPartic)
        {
            string sqlp = @"select * from INcAData where typeid !=-1 and SubParticles is not 'IsSubParticle' and SubParticles is not null ";
            if (fieldAndPartic != "")
            {
                sqlp = sqlp + " and '" + fieldAndPartic + "' like ('%,'||fieldid||'-'||particleid||',%')";
            }
            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            return DT;
        }
        /// <summary>
        /// 查找所有颗粒的颜色、面积、种类名称信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetParticleTypeInformation()
        {
            string sqlp = @"select Area,TypeName,TypeColor from IncAData";
            DataTable dt = dbHelper.ExecuteDataTable(sqlp, null);
            return dt;
        }
        /// <summary>
        /// 查找IncaData表中所有种类
        /// </summary>
        /// <returns></returns>
        public DataTable GetParticleClassAll()
        {
            string sqlp = @"SELECT DISTINCT TypeName FROM IncaData";
            DataTable dt = dbHelper.ExecuteDataTable(sqlp, null);
            return dt;
        }

        /// <summary>
        /// 获取所有Particle
        /// </summary>
        /// <returns></returns>
        public DataTable GetParticleAllHaveXray(string fieldAndPartic)
        {
            string incaSql = @"select * from IncAData where typeid !=-1 and typeid !=4";

            if (fieldAndPartic != "")
            {
                incaSql = incaSql + " and '" + fieldAndPartic + "' like ('%,'||fieldid||'-'||particleid||',%')";
            }
            DataTable incaDT = dbHelper.ExecuteDataTable(incaSql, null);
            incaDT.Columns.Add("Element");

            DataTable dt_element = GetElementChemistry();
            DataTable elementchemistry = dt_element.Clone();
            for (int i = 0; i < dt_element.Rows.Count; i++)
            {
                if (dt_element.Rows[i]["Name"].ToString() != "Fe")
                {
                    elementchemistry.Rows.Add(dt_element.Rows[i].ItemArray);
                }
            }
            for (int i = 0; i < incaDT.Rows.Count; i++)
            {
                string str = "XRayId = " + incaDT.Rows[i]["particleId"].ToString() + " and fieldid = " + incaDT.Rows[i]["fieldid"].ToString();
                DataRow[] drs = elementchemistry.Select(str);
                string ConcatenatedString = "";
                for (int j = 0; j < drs.Length; j++)
                {
                    ConcatenatedString += drs[j]["name"] + "-" + ChangeDataToD(drs[j]["Percentage"].ToString()) + ';';
                }
                incaDT.Rows[i]["Element"] = ConcatenatedString;
            }
            return incaDT;
        }



        /// <summary>
        /// 获取所有Particle
        /// </summary>
        /// <returns></returns>
        public DataTable GetParticleHaveXray(string fieldAndPartic)
        {
            string sqlp = @"select *,
                        (select group_concat(name||'-'||Percentage,';')  
                        from ElementChemistry where XRayId	=INcAData.XRayId		and fieldid=INcAData.fieldid	) as Element  from INcAData where xrayid>-1 ";
            if (fieldAndPartic != "")
            {
                sqlp = sqlp + " and '" + fieldAndPartic + "' like ('%,'||fieldid||'-'||particleid||',%')";
            }

            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            for (int i = 0; i < DT.Rows.Count; i++)
            {
                DT.Rows[i]["Area"] = Math.Sqrt((double)DT.Rows[i]["Area"] / Math.PI) * 2;
            }
            return DT;
        }

        /// <summary>
        /// 获取所有Particle
        /// </summary>
        /// <returns></returns>
        public List<Particle> GetParticleAllList()
        {

            string sqlp = @"select * from IncAData";

            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            List<Particle> listp = dbHelper.TableToList<Particle>(DT);
            return listp;

        }
		/// <summary>
		/// 获取所有Particle
		/// </summary>
		/// <returns></returns>
		public DataTable GetParticleAllList_DataTable()
		{

			string sqlp = @"select * from INcAData";

			DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
			return DT;

		}

		/// <summary>
		/// 获取所有分类的面积
		/// </summary>
		/// <param name="fieldAndPartic">选择颗粒</param>
		/// <returns></returns>
		public DataTable GetAreaByAllIncA(string fieldAndPartic)
        {
            string sqlp = @"select TypeId,TypeName,GroupId,sum(area) as ar,count(1) as con ,GroupName from INcAData where typeid !=-1 and typeid !=4 and ParticleId > -1 and SubParticles is not 'IsSubParticle' ";
            if (fieldAndPartic != "")
            {
                sqlp = sqlp + " and '" + fieldAndPartic + "' like ('%,'||fieldid||'-'||particleid||',%')";
            }
            sqlp = sqlp + "  group by TypeId";

            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            return DT;
        }

        /// <summary>
        /// 获取所有大颗粒没有的小颗粒分类
        /// </summary>
        /// <param name="fieldAndPartic">选择颗粒</param>
        /// <returns></returns>
        public DataTable GetSmallParticleInfo()
        {
            string sqlp = @"select TypeId,TypeName,TypeColor,sum(area) as area,sum(ParticleQuant) as ParticleQuant from SmallParticleInfo where TypeId not in(select TypeId from INcAData) group by TypeId";

            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            return DT;
        }

        /// <summary>
        /// 获取不同分类的面积
        /// </summary>
        /// <param name="fieldAndPartic">选择颗粒</param>
        /// <returns></returns>
        public DataTable GetAreaByIncA(string TypeId, string fieldAndPartic)
        {
            string sqlp = @"select e.name,sum(e.percentage*p.area) as pc,p.TypeId from ElementChemistry e 
                        inner join INcAData p on e.xrayid=p.xrayid and e.fieldid = p.fieldid where p.TypeId=" + TypeId + " and SubParticles is not 'IsSubParticle' ";
            if (fieldAndPartic != "")
            {
                sqlp = sqlp + " and '" + fieldAndPartic + "' like ('%,'||p.fieldid||'-'||p.particleid||',%')";
            }
            sqlp = sqlp + "  group by e.name";
            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            return DT;
        }

        public DataTable GetAreaByIncA_All()
        {
            string sqlp = @"select e.name,sum(e.percentage*p.area) as pc,p.TypeId from ElementChemistry e 
                    inner join INcAData p on e.xrayid=p.xrayid and e.fieldid = p.fieldid group by e.name where SubParticles is not 'IsSubParticle' ";
            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            return DT;
        }

        /// <summary>
        /// 获取全部的物质大类
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllClass()
        {
            string sqlp = @"select GroupName from IncAData group by GroupName order by count(1) desc";

            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            return DT;
        }
        /// <summary>
        /// 获取所有元素
        /// </summary>
        /// <param name="model">Feature</param>
        /// <returns></returns>
        public DataTable GetAllElement()
        {
            string sqlp = @"select name from ElementChemistry group by name order by count(1) desc";

            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            return DT;
        }

        /// <summary>
        /// 获取常用夹杂物分类信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetCommonlyUsedClassifyData()
        {
            string sqlp = @"select 
                (select count(typeid) from incadata where typeid BETWEEN   10100 and 10199 and typeid BETWEEN 12200 and  12299 and typeid BETWEEN 11300 and 11299 ) as SPINEL ,
                (select count(typeid) from incadata where typeid BETWEEN 10000 and 10999 ) as OXIDE ,
                (select count(typeid) from incadata where typeid BETWEEN 11200 and 11299 and typeid BETWEEN 11400 and 11499 and typeid BETWEEN 11200 and 11599 ) as SULFIDE_OXIDE ,
                (select count(typeid) from incadata where typeid BETWEEN 12000 and 12999 ) as NITRIDE ,
                (select count(typeid) from incadata where typeid BETWEEN 11000 and 11999 ) as SULFIDE ";
            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            return DT;
        }
        public bool DeleteFromData(string fieldid, string particleid)
        {

            string sqlp = @"delete from IncAData where FieldId=" + fieldid + " and ParticleId="
            + particleid;
            if (dbHelper.ExecuteQuery_bool(sqlp))
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        /// <summary>
        /// 获取颗粒信息
        /// </summary>
        /// <param name="model">Feature</param>
        /// <returns></returns>
        public Particle GetParticleByFidAndPid(string fieldid, string particleid)
        {
            string sqlp = @"select *,(select xraydata from xraydata where xrayindex=INcAData.xrayid and fieldid="
            + fieldid + ") as XRayData,(select group_concat(name || '_' || Percentage, ';')from ElementChemistry where XRayId = INcAData.XRayId and fieldid ="
            + fieldid + ") as Element from INcAData where fieldid="
            + fieldid + " and particleid= " + particleid;

            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            List<Particle> listp = dbHelper.TableToList<Particle>(DT);
            Particle particle = new Particle();
            if (listp.Count > 0)
            {
                particle = listp[0];
                List<OTSCommon.DBOperate.Model.Element> ElementList = new List<OTSCommon.DBOperate.Model.Element>();
                string element = DT.Rows[0]["Element"].ToString();
                for (int i = 0; i < element.Split(';').Count(); i++)
                {
                    if (element.Split(';')[i] != "")
                    {
                        OTSCommon.DBOperate.Model.Element ele = new OTSCommon.DBOperate.Model.Element() { Name = element.Split(';')[i].Split('_')[0], Percentage = Convert.ToDouble(element.Split(';')[i].Split('_')[1]) };
                        ElementList.Add(ele);
                    }
                }
                particle.ElementList = ElementList;
            }

            return particle;
        }
        public Particle GetParticleXrayDataByFidAndPid(string fieldid, string xrayid)
        {
            string sqlp = @"select xraydata from xraydata where xrayindex=" + xrayid + " and fieldid="
            + fieldid;

            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            List<Particle> listp = dbHelper.TableToList<Particle>(DT);
            if (listp.Count > 0)
            {
                return listp[0];
            }
            else
            {
                return null;
            }


        }
        public List<Segment> GetSegmentData(int fldId,int partId)
        {
            string strs = @"select *  from Segment where FieldId=" + fldId.ToString() + " and ParticleId=" + partId.ToString();
            DataTable DTS = dbHelper.ExecuteDataTable(strs, null);
            DataRow[] dd = DTS.Select();
            List<Segment> flist = dbHelper.RowsToList<Segment>(dd);
            return flist;
        }

        #region 分页添加读取数据库函数
        /// <summary>
        /// 获取分页查询所需信息
        /// </summary>
        /// <param name=""></param>
        /// <param name=""></param>
        /// <returns></returns>
        public DataTable GetInfoForPartucleDevidePage(int currentPage, int pagesize, string OrderFunction, string condition)
        {
            int p = (currentPage - 1) * pagesize;
            string sqliteString = "select fieldid,particleid,AveGray,RectLeft,RectTop,RectWidth,RectHeight,Area,PosX,PosY,TypeId,SegmentNum,FieldPosX as 'SEMPosX',FieldPosY as 'SEMPosY',ParticleId,DMAX,DMIN,DPERP,PERIMETER,ORIENTATION,DINSCR,DMEAN,DELONG,DFERET,TypeName,TypeColor,SubParticles, (select group_concat(name || '-' || Percentage, ';') from ElementChemistry where XRayId = MergedParticleInfo.XRayId and fieldid = MergedParticleInfo.fieldid) as Element from MergedParticleInfo where 1=1 " + condition + " union select fieldid,particleid,AveGray,RectLeft,RectTop,RectWidth,RectHeight,Area,PosX,PosY,TypeId,SegmentNum,FieldPosX,FieldPosY,ParticleId,DMAX,DMIN,DPERP,PERIMETER,ORIENTATION,DINSCR,DMEAN,DELONG,DFERET,TypeName,TypeColor,'' as SubParticles,(select group_concat(name || '-' || Percentage, ';') from ElementChemistry where XRayId = INcAData.XRayId and fieldid = INcAData.fieldid  ) as Element from INcAData where xrayid > -1 and instr(','||(select ifnull(group_concat(SubParticles, ','),'') from MergedParticleInfo)|| ',',',' || fieldid || ':' || particleid || ',')= 0 " + condition + " order by " + OrderFunction + " limit " + pagesize.ToString() + " offset " + p.ToString();
            DataTable DT = new DataTable();
            DT = dbHelper.ExecuteQuery(sqliteString);
            return DT;
        }
        public DataTable GetIncaSurfaceData( List<string> lst_str)
        {
            DataTable particlesAll = GetInfoForPartucleDevidePage2("");
            DataTable dt_element = GetElementChemistry();
            List<int> list_int = new List<int>();

            for (int a = 0; a < lst_str.Count; a++)
            {
                for (int i = 0; i < dt_element.Rows.Count; i++)
                {
                    if (dt_element.Rows[i]["Name"].ToString() == lst_str[a].ToString())
                    {
                        list_int.Add(i);
                    }
                }
            }

            for (int i = 0; i < list_int.Count; i++)
            {
                dt_element.Rows[list_int[i]].Delete();
            }
            dt_element.AcceptChanges();

            for (int i = 0; i < particlesAll.Rows.Count; i++)
            {
                string str = "XRayId = " + particlesAll.Rows[i]["particleId"].ToString() + " and fieldid = " + particlesAll.Rows[i]["fieldid"].ToString();
                DataRow[] drs = dt_element.Select(str);
                string ConcatenatedString = "";
                for (int j = 0; j < drs.Length; j++)
                {
                    ConcatenatedString += drs[j]["name"] + "-" + ChangeDataToD(drs[j]["Percentage"].ToString()) + ';';
                }
                particlesAll.Rows[i]["Element"] = ConcatenatedString;
			}
            return particlesAll;
        }

        public DataTable AddElementColumn(DataTable particlesAll, c_TemplateClass m_mbszclass,DataTable spliceAll)
        {
            if (!particlesAll.Columns.Contains("Element"))
            {
                particlesAll.Columns.Add("Element");

			}

				DataTable dt_element = GetElementChemistry();

            for (int i = 0; i < particlesAll.Rows.Count; i++)
            {

                if (particlesAll.Rows[i]["SubParticles"].ToString() == "")
                {
                    string str = "XRayId = " + particlesAll.Rows[i]["particleId"].ToString() + " and fieldid = " + particlesAll.Rows[i]["fieldid"].ToString();
                    DataRow[] drs = dt_element.Select(str);
                    string ConcatenatedString = "";
                    for (int j = 0; j < drs.Length; j++)
                    {
                        //判断是否在处理元素表中
                        bool bl = false;
                        for (int a = 0; a < m_mbszclass.M_KLLBXX.list_str_kllb_qcys.Count; a++)
                        {
                            if (drs[j]["name"].ToString() == m_mbszclass.M_KLLBXX.list_str_kllb_qcys[a].ToString())
                            {
                                bl = true;
                            }
                        }
                        if (!bl)
                        {
                            ConcatenatedString += drs[j]["name"] + "-" + ChangeDataToD(drs[j]["Percentage"].ToString()) + ';';
                        }
                    }
                    particlesAll.Rows[i]["Element"] = ConcatenatedString;
                }
                else
                {
					for (int a = 0; a < spliceAll.Rows.Count; a++)
					{
						if (particlesAll.Rows[i]["particleId"].ToString() == spliceAll.Rows[a]["particleId"].ToString() && particlesAll.Rows[i]["fieldid"].ToString() == spliceAll.Rows[a]["fieldid"].ToString())
						{
                            particlesAll.Rows[i]["Element"] = spliceAll.Rows[a]["Element"].ToString();
						}
					}
				}
                
            }
            return particlesAll;
        }
        /// <summary>
        /// 拼接颗粒
        /// </summary>
        /// <param name="lst_str"></param>
        /// <returns></returns>
        public DataTable GetSplicingParticlesData()
        {
			DataTable particlesAll = GetSplicingParticles();
			return particlesAll;
		}


		/// <summary>
		/// 保留两位小数
		/// </summary>
		/// <param name="strData"></param>
		/// <returns></returns>
		private string ChangeDataToD(string strData)
        {
            Decimal dData = 0.00M;
            if (strData.Contains("E"))
            {
                dData = Convert.ToDecimal(Decimal.Parse(strData.ToString(), System.Globalization.NumberStyles.Float));
            }
            else
            {
                return Convert.ToDouble(strData).ToString("0.00");
            }
            return Convert.ToDouble(dData).ToString("0.00");
        }
        public DataTable GetInfoForPartucleDevidePage2(string condition)
        {
            string sqliteString = "select fieldid,particleid,AveGray,RectLeft,RectTop,RectWidth,RectHeight,Area,PosX,PosY,TypeId,SegmentNum,FieldPosX " +
                "as 'SEMPosX',FieldPosY as 'SEMPosY',XrayId,DMAX,DMIN,DPERP,PERIMETER,ORIENTATION,DINSCR,DMEAN,DELONG,DFERET,TypeName,TypeColor,SubParticles," +
                " (select group_concat(name || '-' || Percentage, ';') from ElementChemistry where XRayId = MergedParticleInfo.XRayId and fieldid = MergedParticleInfo.fieldid) " +
                "as Element from MergedParticleInfo where 1=1 " + condition + " union select fieldid,particleid,AveGray,RectLeft,RectTop,RectWidth,RectHeight,Area,PosX,PosY,TypeId," +
                "SegmentNum,SEMPosX,SEMPosY,XrayId,DMAX,DMIN,DPERP,PERIMETER,ORIENTATION,DINSCR,DMEAN,DELONG,DFERET,TypeName,TypeColor,'' as SubParticles,'' " +
                "as Element from INcAData where xrayid > -1 and instr(','||(select ifnull(group_concat(SubParticles, ','),'') from MergedParticleInfo)|| ',',',' || fieldid || ':' || particleid || ',')= 0 " +
                condition;
            DataTable DT = new DataTable();
            DT = dbHelper.ExecuteQuery(sqliteString);
            return DT;
        }

        public DataTable GetSplicingParticles()
        {
			string sqliteString1 = "select * from MergedParticleInfo";
			DataTable DT = new DataTable();
			DT = dbHelper.ExecuteQuery(sqliteString1);
			return DT;
		}

		public DataTable GetInfoForPartucleDevidePage_analyticalParticle(string condition)
        {
            string sqliteString = "select fieldid,particleid,AveGray,RectLeft,RectTop,RectWidth,RectHeight,Area,PosX,PosY,TypeId,SegmentNum,SEMPosX,SEMPosY,XrayId,DMAX,DMIN,DPERP,PERIMETER,ORIENTATION,DINSCR,DMEAN,DELONG,DFERET,SubParticles,TypeName,TypeColor,'' as Element from INcAData where (xrayid > -1 and typeid !=9 and typeid !=-1 and typeid !=4 and SubParticles is not 'IsSubParticle') " +
                condition;
            DataTable DT = new DataTable();
            DT = dbHelper.ExecuteQuery(sqliteString);
            return DT;
        }


        public DataTable GetInfoForPartucleDevidePage_otherParticle(string condition)
        {
            string sqliteString = "select fieldid,particleid,AveGray,RectLeft,RectTop,RectWidth,RectHeight,Area,PosX,PosY,TypeId,SegmentNum,SEMPosX,SEMPosY,XrayId,DMAX,DMIN,DPERP,PERIMETER,ORIENTATION,DINSCR,DMEAN,DELONG,DFERET,SubParticles,TypeName,TypeColor,'' as Element from INcAData where (xrayid > -1  and SubParticles is not 'IsSubParticle' and (typeid =9 or typeid =-1 or typeid =4)) " +
                condition;
            DataTable DT = new DataTable();
            DT = dbHelper.ExecuteQuery(sqliteString);
            return DT;
        }
        /// <summary>
        /// 获取拼接颗粒
        /// </summary>
        /// <param name="condition">帅选条件</param>
        /// <returns></returns>
        public DataTable GetInfoForPartucleDevidePage_mergeParticles(string condition)
        {
            string sqliteString = "select fieldid,particleid,AveGray,RectLeft,RectTop,RectWidth,RectHeight,Area,PosX,PosY,TypeId,SegmentNum,SEMPosX,SEMPosY,XrayId,DMAX,DMIN,DPERP,PERIMETER,ORIENTATION,DINSCR,DMEAN,DELONG,DFERET,SubParticles,TypeName,TypeColor,'' as Element from INcAData where (SubParticles Is not null and SubParticles is not 'IsSubParticle' )" + condition;
            DataTable DT1 = new DataTable();
            try
            {
                DT1 = dbHelper.ExecuteQuery(sqliteString);
            }
            catch
            {

            }
            return DT1;
        }


        public DataTable GetInfoForPartucleDevidePage_allParticles(string condition)
        {
            string sqliteString = "select fieldid,particleid,AveGray,RectLeft,RectTop,RectWidth,RectHeight,Area,PosX,PosY,TypeId,SegmentNum,SEMPosX,SEMPosY,XrayId,DMAX,DMIN,DPERP,PERIMETER,ORIENTATION,DINSCR,DMEAN,DELONG,DFERET,SubParticles,TypeName,TypeColor,'' as Element from INcAData  where xrayid > -1 and SubParticles is not 'IsSubParticle' " +
                condition;
            DataTable DT = new DataTable();
            DT = dbHelper.ExecuteQuery(sqliteString);
            return DT;
        }
        
        
        
        
        
        
        
        
        
        public DataTable GetInfoForPartucleDevidePage_NotIdentifyParticle(string condition)
        {
            string sqliteString = "select fieldid,particleid,AveGray,RectLeft,RectTop,RectWidth,RectHeight,Area,PosX,PosY,TypeId,SegmentNum,SEMPosX,SEMPosY,XrayId,DMAX,DMIN,DPERP,PERIMETER,ORIENTATION,DINSCR,DMEAN,DELONG,DFERET,SubParticles,TypeName,TypeColor,'' as Element from INcAData where xrayid > -1 and typeid =9 and SubParticles is not 'IsSubParticle' " +
                condition;
            DataTable DT = new DataTable();
            DT = dbHelper.ExecuteQuery(sqliteString);
            return DT;
        }
        public DataTable GetInfoForPartucleDevidePage_InvalidParticle(string condition)
        {
            string sqliteString = "select fieldid,particleid,AveGray,RectLeft,RectTop,RectWidth,RectHeight,Area,PosX,PosY,TypeId,SegmentNum,SEMPosX,SEMPosY,XrayId,DMAX,DMIN,DPERP,PERIMETER,ORIENTATION,DINSCR,DMEAN,DELONG,DFERET,SubParticles,TypeName,TypeColor,'' as Element from INcAData where xrayid > -1 and typeid =-1 and SubParticles is not 'IsSubParticle' " +
                condition;
            DataTable DT = dbHelper.ExecuteQuery(sqliteString);
            return DT;
        }
        public DataTable GetInfoForPartucleDevidePage_LowCountsParticle(string condition)
        {
            string sqliteString = "select fieldid,particleid,AveGray,RectLeft,RectTop,RectWidth,RectHeight,Area,PosX,PosY,TypeId,SegmentNum,SEMPosX,SEMPosY,XrayId,DMAX,DMIN,DPERP,PERIMETER,ORIENTATION,DINSCR,DMEAN,DELONG,DFERET,SubParticles,TypeName,TypeColor,'' as Element from INcAData where xrayid > -1 typeid =4 and SubParticles is not 'IsSubParticle' " +
                condition;
            DataTable DT = dbHelper.ExecuteQuery(sqliteString);
            return DT;
        }
        public DataTable GetClassificationOfAllParticles(string condition)
        {
            string sqliteString1 = "select distinct TypeName from INcAData " + condition;
            DataTable DT = dbHelper.ExecuteQuery(sqliteString1);
            return DT;
        }
        public DataTable GetXRayData()
        {
            string sqliteString = @"select * from xraydata";
            DataTable DT = new DataTable();
            DT = dbHelper.ExecuteQuery(sqliteString);
            return DT;
        }

        public DataTable GetElementChemistry()
        {
            string sqliteString = "select * from ElementChemistry";
            DataTable DT = new DataTable();
            DT = dbHelper.ExecuteQuery(sqliteString);
            return DT;
        }
        /// <summary>
        /// 得到图形形状
        /// </summary>
        /// <returns></returns>
        public DataTable GetSegment()
        {
            string sqliteString = "select * from Segment";
            DataTable DT = new DataTable();
            DT = dbHelper.ExecuteQuery(sqliteString);
            return DT;

        }











































        /// <summary>
        /// 全部颗粒不带元素
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataTable GetAllParticleWithMergeParticlesWithoutEle(string condition)
        {
            DataTable particlesAll = GetParticleAllforparticlelist(condition);
            return particlesAll;
        }




        /// <summary>
        /// 全部颗粒带元素
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataTable GetAllParticleWithMergeParticles(string condition)
        {
            DataTable particlesAll=new DataTable();
            DataTable particlesAll1 = GetInfoForPartucleDevidePage_allParticles(condition);
            DataTable mergeParticles = GetInfoForPartucleDevidePage_mergeParticles(condition);
            if (mergeParticles != null && mergeParticles.Rows.Count > 0)
            {
                RemoveMergeParticles(ref particlesAll1, mergeParticles);
            }
            particlesAll = particlesAll1.Copy();
            foreach (DataRow item in mergeParticles.Rows)
            {
                particlesAll.ImportRow(item);
            }
            DataTable elementchemistry = GetElementChemistry();
            for (int i = 0; i < particlesAll.Rows.Count; i++)
            {
                string str = "XRayId = " + particlesAll.Rows[i]["particleId"].ToString() + " and fieldid = " + particlesAll.Rows[i]["fieldid"].ToString();
                DataRow[] drs = elementchemistry.Select(str);
                string ConcatenatedString = "";
                for (int j = 0; j < drs.Length; j++)
                {
                    ConcatenatedString += drs[j]["name"] + "-" + drs[j]["Percentage"] + ';';
                }
                particlesAll.Rows[i]["Element"] = ConcatenatedString;
            }
            return particlesAll;
        }

        
        public void RemoveMergeParticles(ref DataTable dataTable_Particle, DataTable dataTable_MergeParticles)
        {
            foreach (DataRow row in dataTable_Particle.Rows)
            {
                foreach (DataRow MergeParticlesRow in dataTable_MergeParticles.Rows)
                {
                    string subParticleString = MergeParticlesRow["SubParticles"].ToString();
                    string[] sub = subParticleString.Split(',');
                    if (row.RowState == DataRowState.Deleted)
                    {
                        break;
                    }
                    if (row["fieldid"].ToString() == Convert.ToString(sub[0]).Split(':')[0] && row["ParticleId"].ToString() == Convert.ToString(sub[0]).Split(':')[1])
                    {
                        row.Delete();
                        break;
                    }
                    if (row["fieldid"].ToString() == Convert.ToString(sub[1]).Split(':')[0] && row["ParticleId"].ToString() == Convert.ToString(sub[1]).Split(':')[1])
                    {
                        row.Delete();
                        break; ;
                    }
                }
            }
            dataTable_Particle.AcceptChanges();
        }
        public void InsertUpdate(List<int> particleID, List<string> particleData)
        {
            List<KeyValuePair<string, SQLiteParameter[]>> cmdlist = new List<KeyValuePair<string, SQLiteParameter[]>>();
            var str = dbHelper.UpdateINCAEntryData(particleID, particleData);
            cmdlist.Add(str);
            try
            {
                dbHelper.ExecuteNonQueryBatch(ref cmdlist);

            }
            catch (Exception e)
            {
                //NLog.LogManager.GetCurrentClassLogger().Error(e.Message);

            }
        }

        public KeyValuePair<string, SQLiteParameter[]> GetUpdataAIncACmd(List<int> particleID, List<string> particleData)
        {
            var str = dbHelper.UpdateINCAEntryData(particleID, particleData);
            return str;
        }
        public void ExecuteNonQueryBatch(List<KeyValuePair<string, SQLiteParameter[]>> cmdlist)
        {
            try
            {
                dbHelper.ExecuteNonQueryBatch(ref cmdlist);

            }
            catch (Exception e)
            {
                //NLog.LogManager.GetCurrentClassLogger().Error(e.Message);

            }
        }
        /// <summary>
        /// 传入颗粒的tagid和fieldid,来获取该颗粒下对应的xray数据
        /// </summary>
        /// <param name="in_clr_tagid">颗粒id</param>
        /// <param name="in_clr_fieldid"></param>
        /// <param name="Analysis_xray"></param>
        public Particle GetXrayByParticleTagIDAndFieldID_ForMergeParticle(int in_clr_tagid, int in_clr_fieldid, out uint[] Analysis_xray)
        {
            Analysis_xray = new uint[2000];
            Particle particle = new Particle();
            particle = GetMergedParticleInfo(in_clr_fieldid, in_clr_tagid, out Analysis_xray);
            return particle;
        }
        public Particle GetXrayByParticleIDAndFieldID(string subParticleString, int in_clr_tagid, int in_clr_fieldid, out uint[] Analysis_xray)
        {
            if (subParticleString != "" && subParticleString != null)
            {
                return GetXrayByParticleTagIDAndFieldID_ForMergeParticle(in_clr_tagid, in_clr_fieldid, out Analysis_xray);
            }
            else
            {
                return GetXrayByParticleTagIDAndFieldID_ForUncombinedParticle(in_clr_tagid, in_clr_fieldid, out Analysis_xray);
            }
        }
        public Particle GetXrayByParticleTagIDAndFieldID_ForUncombinedParticle(int in_clr_tagid, int in_clr_fieldid, out uint[] Analysis_xray)
        {
            string sqlp = @"select *,
                            (select group_concat(name||'-'||Percentage,';')  
                            from ElementChemistry where XRayId	=IncAData.XRayId		and fieldid=IncAData.fieldid	) as Element
                            from 	IncAData  where FieldId=" + in_clr_fieldid.ToString() + " and ParticleId=" + in_clr_tagid.ToString();
            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            List<Particle> listp = dbHelper.TableToList<Particle>(DT);
            Particle pte = new Particle();
            Analysis_xray = new uint[2000];
            if (listp.Count > 0)
            {
                pte = listp[0];
                List<OTSCommon.DBOperate.Model.Element> ElementList = new List<OTSCommon.DBOperate.Model.Element>();
                string element = DT.Rows[0]["Element"].ToString();
                for (int i = 0; i < element.Split(';').Count(); i++)
                {
                    string elestr = element.Split(';')[i];
                    if (elestr != "")
                    {
                        OTSCommon.DBOperate.Model.Element ele = new OTSCommon.DBOperate.Model.Element() { Name = elestr.Split('-')[0], Percentage = Convert.ToDouble(elestr.Split('-')[1]) };
                        ElementList.Add(ele);
                    }
                }
                pte.ElementList = ElementList;
                Analysis_xray = new uint[2000];
                var particle = GetParticleXrayDataByFidAndPid(Convert.ToString(in_clr_fieldid), Convert.ToString(in_clr_tagid));
                
                pte.XRayData = particle.XRayData;
                if (particle != null)
                {
                    if (particle.XrayId > -1)
                    {
                        for (int i = 0; i < 2000; i++)
                        {
                            Analysis_xray[i] = BitConverter.ToUInt32(particle.XRayData, i * 4);
                        }
                    }
                }
            }
            return pte;
        }
        public List<ShowElementInfo> GetShowElementInfos(List<Element> list_celementchemistryclr)
        {
            List<ShowElementInfo> list_showelementinfo = new List<ShowElementInfo>();
            for (int i = 0; i < list_celementchemistryclr.Count; i++)
            {
                ShowElementInfo ls_sei = new ShowElementInfo();
                ls_sei.ElementName = list_celementchemistryclr[i].Name;
                ls_sei.Percentage = list_celementchemistryclr[i].Percentage;
                ls_sei.dKF = Convert.ToDouble(CListPeriodic.GetPeriodicByEleName(ls_sei.ElementName).K_Peak);
                double de_sx2 = 0;
                if (CListPeriodic.GetPeriodicByEleName(ls_sei.ElementName).L_Peak == "" || CListPeriodic.GetPeriodicByEleName(ls_sei.ElementName).L_Peak == "-")
                {
                    de_sx2 = 0;
                }
                else
                {
                    de_sx2 = Convert.ToDouble(CListPeriodic.GetPeriodicByEleName(ls_sei.ElementName).L_Peak);
                }
                ls_sei.dLF = de_sx2;
                list_showelementinfo.Add(ls_sei);
            }
            list_showelementinfo.Sort((p1, p2) => p2.Percentage.CompareTo(p1.Percentage));
            return list_showelementinfo;
        }
        
        #endregion
       
        public ParticleData()
        { 

        }
		
		
	}
}