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

using OTSCommon.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Linq;

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 con, string max, string min, int display)
        {

            //string sqlp = "select a.*,b.XrayData from IncAData a left join XRayData b on a.FieldId =b.FieldId and a.XrayId= b.XrayIndex";
            //string where = " where 1=1 ";
            string sqlp = "select a.* from IncAData a ";
            string where = " where 1=1 ";
            if (display == 1)
            {
                where = where + " and a.XrayId >-1 ";
            }
            if (con != "")
            {
                where = where + " and a." + con + ">" + min + " and a." + con + "<" + 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 DataTable GetMergedParticleInfo()
        {

            string sqlp = @"select *,
                            (select group_concat(name||'-'||Percentage,';')  
                            from ElementChemistry where XRayId	=MergedParticleInfo.XRayId		and fieldid=MergedParticleInfo.fieldid	) as Element
                            from 	MergedParticleInfo ";

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

        /// <summary>
        /// 获取ParticleList
        /// </summary>
        /// <param name="model">Feature</param>
        /// <returns></returns>
        public DataTable GetParticleListByIncA(string con)
        {
            string sqlp = @"select TypeId,TypeName,TypeColor,count(1) as con,sum(Area) as Area,avg(" + con
                + ") as av,max(" + con
                + ") as max ";
            sqlp = sqlp + " from IncAData group by TypeId";

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

        /// <summary>
        /// 获取ParticleList
        /// </summary>
        /// <param name="model">Feature</param>
        /// <returns></returns>
        public DataTable GetParticleListForParticlSize(string con, string fieldAndPartic)
        {

            string sqlp = @"select TypeId,TypeName,TypeColor,count(1) as con,avg(" + con
                + ") as av,max(" + con
                + ") as max ,GroupName";
            sqlp = sqlp + " from IncAData ";
            if (fieldAndPartic != "")
            {
                sqlp = sqlp + " where '" + fieldAndPartic + "' like ('%,'||fieldid||'-'||particleid||',%')";
            }

            sqlp = sqlp + " group by TypeId ";

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

            if (con == "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";
            if (fieldAndPartic != "")
            {
                sqlp = sqlp + " where '" + 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";
            if (fieldAndPartic != "")
            {
                sqlp = sqlp + " where '" + 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>
        /// 查找所有颗粒的颜色、面积、种类名称信息
        /// </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";
          
            if (fieldAndPartic != "")
            {
                incaSql = incaSql + " where '" + 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>
        /// 获取所有分类的面积
        /// </summary>
        /// <param name="fieldAndPartic">选择颗粒</param>
        /// <returns></returns>
        public DataTable GetAreaByAllIncA(string fieldAndPartic)
        {
            string sqlp = @"select TypeId,TypeName,sum(area) as ar,count(1) as con ,GroupName from INcAData";
            if (fieldAndPartic != "")
            {
                sqlp = sqlp + " where '" + 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 + " ";
            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 ";
            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<Element> ElementList = new List<Element>();
                string element = DT.Rows[0]["Element"].ToString();
                for (int i = 0; i < element.Split(';').Count(); i++)
                {
                    if (element.Split(';')[i] != "")
                    {
                        Element ele = new 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 enum PARTCLE_TYPE
        {
            SMALL = 0,//过小颗粒
            OVERSIZE = 1,//过大颗粒
            AVE_GRAY_NOT_INRANRE = 2,//亮度不在分析范围内的颗粒
            SEARCH_X_RAY = 3,
            LOW_COUNT = 4,//低计数率颗粒
            NO_INTEREST_ELEMENTS = 5,
            NO_ANALYSIS_X_RAY = 6,//不含分析元素
            NOT_IDENTIFIED_SIC = 7,//非夹杂物颗粒SiC
            NOT_IDENTIFIED_FEO = 8,//非夹杂物颗粒FeO
            NOT_IDENTIFIED = 9,//未识别颗粒
            IDENTIFIED = 10,//分析颗粒，当为可识别类型时，可以被进一步识别为用户类型（1000以上），系统预定义类型（10000以上），所以最终颗粒类型不会为8，但可能为7

            USER_DEFINED_MIN = 1000,
            SYS_DEFINED_MIN = 10000
        }

        #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(DataTable dt,int sel,List<string> lst_str)
        {
            DataTable particlesAll = new DataTable();
            if (sel==0)
            {
                particlesAll = GetInfoForPartucleDevidePage2("");
            }
            else
            {
                particlesAll = dt.Copy();
                particlesAll.Columns.Add("Element");
            }
   

            DataTable dt_element = GetElementChemistry();
            DataTable elementchemistry = dt_element.Copy();

          
            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 (elementchemistry.Rows[i]["Name"].ToString() == lst_str[a].ToString())
                    {
                        list_int.Add(i);
                    }
                }
            }
            
            for (int i=0;i< list_int.Count;i++)
            {
                elementchemistry.Rows[list_int[i]].Delete();
            }
            elementchemistry.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 = elementchemistry.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;
            }



            //string sqlliteString = "select * from INcAData";
            //DataTable dt = dbHelper.ExecuteQuery(sqlliteString);
            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 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,TypeName,TypeColor,'' as Element from INcAData where xrayid > -1  and typeid !=9 and typeid !=-1 " +
                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,TypeName,TypeColor,'' as Element from INcAData where xrayid > -1  and (typeid =9 or typeid =-1) " +
                condition;
            DataTable DT = new DataTable();
            DT = dbHelper.ExecuteQuery(sqliteString);
            return DT;
        }

        public DataTable GetInfoForPartucleDevidePage_mergeParticles(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,''as Element from MergedParticleInfo where 1=1 " + condition;
            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;
        }

        public Bitmap GetBitmapForBig(string sub, double xs, string path,int picHeight,int picWidth)
        {
            string vs = "," + sub.Replace(':', '-') + ",";

            DataTable dataTable = GetParticleAll(vs);
            if (dataTable.Rows.Count == 0)
            {
                return null;
            }

            //内接矩形
            double max_Y = Convert.ToInt64(dataTable.Rows[0]["FieldPosY"]) * xs - Convert.ToInt64(dataTable.Rows[0]["RectTop"]);
            double max_X = Convert.ToInt64(dataTable.Rows[0]["FieldPosX"]) * xs + Convert.ToInt64(dataTable.Rows[0]["RectLeft"]);
            double min_Y = max_Y;
            double min_X = max_X;

            //拼接field矩形
            double MAX_Y = Convert.ToInt64(dataTable.Rows[0]["FieldPosY"]) * xs;
            double MAX_X = Convert.ToInt64(dataTable.Rows[0]["FieldPosX"]) * xs;
            double MIN_Y = MAX_Y;
            double MIN_X = MAX_X;
            foreach (DataRow item in dataTable.Rows)
            {
                //颗粒外接矩形
                double lefttopX = Convert.ToInt64(item["FieldPosX"]) * xs + Convert.ToInt64(item["RectLeft"]);
                if (lefttopX < min_X)
                {
                    min_X = lefttopX;
                }
                if (lefttopX + Convert.ToInt64(item["RectWidth"]) > max_X)
                {
                    max_X = lefttopX + Convert.ToInt64(item["RectWidth"]);
                }
                double lrfttopY = Convert.ToInt64(item["FieldPosY"]) * xs - Convert.ToInt64(item["RectTop"]);
                if (max_Y < lrfttopY)
                {
                    max_Y = lrfttopY;
                }
                if (min_Y > lrfttopY - Convert.ToInt64(item["RectHeight"]))
                {
                    min_Y = lrfttopY - Convert.ToInt64(item["RectHeight"]);
                }
                //画布
                double lefttopXH = Convert.ToInt64(item["FieldPosX"]) * xs;
                if (lefttopXH > MAX_X)
                {
                    MAX_X = lefttopXH;
                }
                if (lefttopXH < MIN_X)
                {
                    MIN_X = lefttopXH;
                }
                double lrfttopYH = Convert.ToInt64(item["FieldPosY"]) * xs;
                if (MAX_Y < lrfttopYH)
                {
                    MAX_Y = lrfttopYH;
                }
                if (MIN_Y > lrfttopYH)
                {
                    MIN_Y = lrfttopYH;
                }
            }
            int WIDTH = Convert.ToInt32(MAX_X - MIN_X) + picWidth;
            int HEIGHT = Convert.ToInt32(MAX_Y - MIN_Y) + picHeight;
            //构造最终的图片白板
            Bitmap tableChartImage = new Bitmap(WIDTH, HEIGHT);
            Graphics graph = Graphics.FromImage(tableChartImage);
            //初始化这个大图
            graph.DrawImage(tableChartImage, 0, 0);

            int width = Convert.ToInt32(max_X - min_X);
            int height = Convert.ToInt32(max_Y - min_Y);
            int X = Convert.ToInt32(min_X - MIN_X);
            int Y = Convert.ToInt32(MAX_Y - max_Y);
            Rectangle rectangle = new Rectangle() { X = X, Y = Y, Width = width, Height = height };

            foreach (DataRow item in dataTable.Rows)
            {
                string filePath = path + "\\FIELD_FILES\\";
                string imagePath = filePath + "Field" + item["fieldid"].ToString() + ".bmp";
                //然后将取出的数据，转换成Bitmap对象
                Bitmap ls_bt = ReadImageFile(imagePath);
                int x = Convert.ToInt32(Convert.ToDouble(item["FieldPosX"]) * xs - MIN_X);
                int y = Convert.ToInt32(Convert.ToDouble(item["FieldPosY"]) * xs - MIN_Y);
                
                    graph.DrawImage(ls_bt, x, y);
                
            }

            Bitmap bmap = tableChartImage.Clone(rectangle, PixelFormat.Format8bppIndexed);
            return bmap;
        }

        /// <summary>
        /// 通过FileStream 来打开文件，这样就可以实现不锁定Image文件，到时可以让多用户同时访问Image文件
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public Bitmap ReadImageFile(string path)
        {
            if (!File.Exists(path))
            {
                return null;//文件不存在
            }
            FileStream fs = File.OpenRead(path); //OpenRead
            int filelength = 0;
            filelength = (int)fs.Length; //获得文件长度 
            Byte[] image = new Byte[filelength]; //建立一个字节数组 
            fs.Read(image, 0, filelength); //按字节流读取 
            System.Drawing.Image result = System.Drawing.Image.FromStream(fs);
            fs.Close();
            Bitmap bit = new Bitmap(result);
            return bit;
        }

        /// <summary>
        /// 传入单颗颗粒的particle类对象,返回从field中抠取出的bitmap对象,抠取单颗颗粒
        /// </summary>
        /// <param name="in_cotsparticleclr"></param>
        /// <returns></returns>
        public Bitmap GetBitmapByParticle(Bitmap ls_bt, Rectangle offset_rect)
        {
            //为了能把整个颗粒显示完整
            offset_rect.X = offset_rect.X - 5;
            offset_rect.Y = offset_rect.Y - 5;
            offset_rect.Width = offset_rect.Width + 10;
            offset_rect.Height = offset_rect.Height + 10;

            //防止计算偏差后，有坐标溢出现象
            if (offset_rect.X < 0)
                offset_rect.X = 0;
            if (offset_rect.Y < 0)
                offset_rect.Y = 0;
            if (offset_rect.X + offset_rect.Width > ls_bt.Width)
            {
                offset_rect.Width = ls_bt.Width - offset_rect.X;
            }
            if (offset_rect.Y + offset_rect.Height > ls_bt.Height)
            {
                offset_rect.Height = ls_bt.Height - offset_rect.Y;
            }

            Bitmap new_ret_bp;
            //防止为0后面计算出错
            if (offset_rect.Width > 0 && offset_rect.Height > 0)
            {
                //最后通过list_showsegment组建成新的图片，进行返回
                new_ret_bp = ls_bt.Clone(offset_rect, PixelFormat.Format8bppIndexed);
            }
            else
            {

                new_ret_bp = new Bitmap(offset_rect.Width, offset_rect.Height);
            }

            return new_ret_bp;
        }

        #endregion
    }

    public class UserLibraryData
    {
        private SqlHelper dbHelper;

        public UserLibraryData(string libraryName)
        {
            NLog.Logger log = NLog.LogManager.GetCurrentClassLogger();
            if (!libraryName.Contains(".db"))
            {
                libraryName += ".db";
            }
            string fullPath = System.IO.Directory.GetCurrentDirectory() + "\\Config\\SysData\\" + libraryName ;
            if (System.IO.File.Exists(fullPath))
            {
                dbHelper = new SqlHelper("data source='" + fullPath + "'");
            }
            else
            {
                dbHelper = null;
                log.Error("Failed to load user-defined library_"+ fullPath+"!");
            }
        }
        
        public DataTable GetSubAttributeFromDatabase()
        {
            string sqliteString = "select STDId,Hardness,Density,Electrical_conductivity from ClassifySTD";
            DataTable DT = new DataTable();
            DT = dbHelper.ExecuteQuery(sqliteString);
            return DT;
        }

        public SqlHelper GetSqlHelper() { return dbHelper; }
    }
}