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

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<Model.Particle> GetParticleList(Model.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<Model.Particle>();
            foreach (DataRow dr in DT.Rows)
            {
                Model.Particle item = new Model.Particle() { };
                result.Add(item);
            }
            return result;

        }
        /// <summary>
        /// 获取ParticleListBy
        /// </summary>
        /// <param name="model">Feature</param>
        /// <returns></returns>
        public List<Model.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<Model.Particle> listp = dbHelper.TableToList<Model.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 ";
            sqlp = sqlp + " 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>
        /// 获取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);
            return DT;
        }

        /// <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);

            return DT;
        }

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

            string sqlp = @"select * from INcAData";

            DataTable DT = dbHelper.ExecuteDataTable(sqlp, null);
            List<Model.Particle> listp = dbHelper.TableToList<Model.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 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>
        /// <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;
        }

        /// <summary>
        /// 获取颗粒信息
        /// </summary>
        /// <param name="model">Feature</param>
        /// <returns></returns>
        public Model.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<Model.Particle> listp = dbHelper.TableToList<Model.Particle>(DT);
            Model.Particle particle = new Model.Particle();
            if (listp.Count > 0)
            {
                particle = listp[0];
                List<Model.Element> ElementList = new List<Model.Element>();
                string element = DT.Rows[0]["Element"].ToString();
                for (int i = 0; i < element.Split(';').Count(); i++)
                {
                    if (element.Split(';')[i] != "")
                    {
                        Model.Element ele = new 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 Model.Particle GetParticleXrayDataByFidAndPid(string fieldid, string particleid)
        {
            string sqlp = @"select xraydata from xraydata where xrayindex="+particleid+" and fieldid="
            + fieldid ;

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

            return listp[0];
        }
        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,ElementNum,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,ElementNum,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 GetInfoForPartucleDevidePage2(string condition)
        {
            string sqliteString = "select fieldid,particleid,AveGray,RectLeft,RectTop,RectWidth,RectHeight,Area,PosX,PosY,TypeId,ElementNum,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,ElementNum,SegmentNum,FieldPosX,FieldPosY,ParticleId,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 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)
        {
            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) + 1024;
            int HEIGHT = Convert.ToInt32(MAX_Y - MIN_Y) + 768;
            //构造最终的图片白板
            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);
                try
                {
                    graph.DrawImage(ls_bt, x, y);
                }
                catch (Exception e)
                {

                    throw;
                }
            }

            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
    }
}