everlight.eap/backend/DllEapDal/DaSampleDal.cs

using Cksoft.Data;
using Cksoft.Data.Repository;
using DllEapEntity;
using DllEapEntity.Dtos;
using DllEapEntity.OFILM;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;

namespace DllEapDal
{
   public  class DaSampleDal
    {
        private readonly IDatabase Db = null;
        public DaSampleDal(IDatabase db)
        {
            Db=db;
        }
        /// <summary>
        /// 获取DA sample校验参数数据列表以生成时间倒序排列
        /// </summary>
        /// <param name="maccode"></param>
        /// <param name="macnum"></param>
        /// <param name="type"></param>
        /// <param name="result"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public IEnumerable<Sample> Get(string maccode, string macnum, string type, string result, DateTime? start, DateTime? end, int pageIndex, int pageSize, out int total)
        {
            string str = $@"f.FName Factory,
	e.FName Plant,
	d.FName Floor,
	g.PCode PCode,
	b.FCode MacCode,
	h.MacNumber MacNum,
	a.Time Time,
	a.Type Type,
    a.Result Result,
	a.X,
	a.Y,
	a.T,
	a.RecCode,
	i.FName NAME ";
            
			var datas = Db.FindList<Sample>(Sql(maccode, macnum, type, result, start, end, pageIndex, pageSize,str)+$"limit { (pageIndex - 1) * pageSize},{ pageSize}");
            var sampleValue = MesGet();
            foreach (var item in datas)
            {
                var temp = sampleValue.Where(c => c.macId == item.MacCode && c.materialNo == item.Type)?.FirstOrDefault();
                /* item.aXOffset = temp.aXOffset == null ?"0"  : temp.aXOffset;
                 item.sXOffset = temp.aXOffset == null ? "0" : temp.sXOffset;
                 item.wXOffset = temp.wXOffset == null ? "0" : temp.wXOffset;
                 item.aYOffset = temp.aYOffset == null ? "0" : temp.aYOffset;
                 item.sYOffset = temp.sYOffset == null ? "0" : temp.sYOffset;
                 item.wYOffset = temp.wYOffset == null ? "0" : temp.wYOffset;
                 item.aTOffset = temp.aTOffset == null ? "0" : temp.aTOffset;
                 item.sTOffset = temp.sTOffset == null ? "0" : temp.sTOffset;
                 item.wTOffset = temp.wTOffset == null ? "0" : temp.wTOffset;*/
                item.aXOffset = temp.aXOffset ;
                item.sXOffset = temp.sXOffset;
                item.wXOffset = temp.wXOffset ;
                item.aYOffset = temp.aYOffset ;
                item.sYOffset = temp.sYOffset ;
                item.wYOffset = temp.wYOffset;
                item.aTOffset = temp.aTOffset;
                item.sTOffset = temp.sTOffset ;
                item.wTOffset = temp.wTOffset;
            }
            str = "count(1)";
			total = Convert.ToInt32(Db.FindObject(Sql(maccode, macnum, type, result, start, end, pageIndex, pageSize, str)));
			return datas;
           
        }
        public IEnumerable<QisCheck> MesGet()
        {
            using(IDatabase db = DbFactory.Base("qis"))
            {
                return db.FindList<QisCheck>($@"select mac_id macId,Material_NO materialNo,STANDARD_XOFFSET sXOffset,STANDARD_YOFFSET sYOffset,STANDARD_ROTATE sTOffset,WARNING_XOFFSET wXOffset,WARNING_YOFFSET wYOffset,WARNING_ROTATE wTOffset,ABNORMAL_XOFFSET aXOffset,ABNORMAL_YOFFSET aYOffset,ABNORMAL_ROTATE aTOffset  FROM ofg_qis.view_mfg_sample_check where 1=1");

            }
        }
        /// <summary>
        /// 构建DA Sample 数据 sql语句
        /// </summary>
        /// <param name="maccode"></param>
        /// <param name="macnum"></param>
        /// <param name="type"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="str"></param>
        /// <returns></returns>
		public string Sql(string maccode, string macnum, string type,string result,DateTime? start, DateTime? end, int pageIndex, int pageSize, string str)
        {
			string sql = $@"select {str} FROM
	dasample a
	LEFT JOIN machine b ON b.id = a.MacId
	INNER JOIN factoryregion c ON b.RegionId = c.Id
	INNER JOIN factoryregion d ON d.id = c.ParentId
	INNER JOIN factoryregion e ON e.id = d.ParentId
	INNER JOIN factoryregion f ON f.Id = e.ParentId
	LEFT JOIN mactprocess g ON g.MacID = b.ID
	LEFT JOIN machinenumber h ON h.MacId = b.id
	LEFT JOIN staff i ON i.FCode = a.RecCode where 1=1 ";
            if (!string.IsNullOrEmpty(maccode))
            {
				sql += $" and b.fcode like '%{maccode}%'";
            }
			if (!string.IsNullOrEmpty(macnum))
			{
				sql += $" and h.MacNumber like '%{macnum}%'";
			}
			if (!string.IsNullOrEmpty(type))
			{
				sql += $" and a.type like '%{type}%'";
			}
            if (!string.IsNullOrEmpty(result))
            {
                sql += $" and a.result like '%{result}%'";
            }
            if (start.HasValue)
			{
				sql += $" and a.Time > '{start.Value.ToString("yyyy-MM-dd HH:mm:ss")}'";
			}
			if (end.HasValue)
			{
				sql += $" and a.Time < '{end.Value.ToString("yyyy-MM-dd HH:mm:ss")}'";
			}
			sql += $"order By a.Time desc ";
			return sql;
        }
        /// <summary>
        /// 从数据获取DA parameter 数据列表以时间倒序排列
        /// </summary>
        /// <param name="maccode"></param>
        /// <param name="macnum"></param>
        /// <param name="type"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public IEnumerable<Sample> GetParameter(string maccode, string macnum, string type, DateTime? start, DateTime? end, int pageIndex, int pageSize, out int total)
        {
            string str = $@"f.FName Factory,
	e.FName Plant,
	d.FName Floor,
	g.PCode PCode,
	b.FCode MacCode,
	h.MacNumber MacNum,
	a.RecTime Time,
	a.Type Type,
	a.X,
	a.Y,
	a.T,a.MDP,a.ADP,a.RecCode";
            var data = Db.FindList<Sample>(GetSql(maccode, macnum, type, start, end, pageIndex, pageSize, str));
            str = "count(1)";
            total = Convert.ToInt32(Db.FindObject(GetSql(maccode, macnum, type, start, end, pageIndex, pageSize, str)));
            return data;
            
        }
        /// <summary>
        /// 生成DA parameter的sql语句
        /// </summary>
        /// <param name="maccode"></param>
        /// <param name="macnum"></param>
        /// <param name="type"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="str"></param>
        /// <returns></returns>
        public string GetSql(string maccode, string macnum, string type, DateTime? start, DateTime? end, int pageIndex, int pageSize, string str)
        {
            string sql = $@"select {str} 
	FROM
	daparameter a
	LEFT JOIN machine b ON b.id = a.MacId
	INNER JOIN factoryregion c ON b.RegionId = c.Id
	INNER JOIN factoryregion d ON d.id = c.ParentId
	INNER JOIN factoryregion e ON e.id = d.ParentId
	INNER JOIN factoryregion f ON f.Id = e.ParentId
	LEFT JOIN mactprocess g ON g.MacID = b.ID
	LEFT JOIN machinenumber h ON h.MacId = b.id
	where 1=1 ";
            if (!string.IsNullOrEmpty(maccode))
            {
                sql += $" and b.fcode like '%{maccode}%'";
            }
            if (!string.IsNullOrEmpty(macnum))
            {
                sql += $" and h.MacNumber like '%{macnum}%'";
            }
            if (!string.IsNullOrEmpty(type))
            {
                sql += $" and a.type like '%{type}%'";
            }

            if (start.HasValue)
            {
                sql += $" and a.RecTime > '{start.Value.ToString("yyyy-MM-dd HH:mm:ss")}'";
            }
            if (end.HasValue)
            {
                sql += $" and a.RecTime < '{end.Value.ToString("yyyy-MM-dd HH:mm:ss")}'";
            }
            sql += $"order By a.RecTime desc ";
            if (str != "count(1)")
            {
                sql += $"limit { (pageIndex - 1) * pageSize},{ pageSize}";
            }
            return sql;
        }
        /// <summary>
        /// 从DA机台获取参数
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public async Task<object>  GetParams(MacInfo m)
        {
            try
            {
                
                string url = "http://192.168.124.93:8606/eap/api/equipment/getOriginEquipmentParams";
                
                HttpClient client = new HttpClient(new HttpClientHandler
                {
                    AutomaticDecompression = DecompressionMethods.GZip | DecompressionMethods.Deflate
                });
                client.DefaultRequestHeaders.Add("Method", "POST");
                client.DefaultRequestHeaders.Add("ContentType", "multipart/form-data; charset=utf-8");
                var content = new MultipartFormDataContent();
                var uri = new Uri(url);
                var equipmentId = new ByteArrayContent(Encoding.UTF8.GetBytes(m.EquipmentID));
                content.Add(equipmentId, "EquipmentID");
                for (int i = 0; i < m.parameters.Count(); i++)
                {
                    var temp = new ByteArrayContent(Encoding.UTF8.GetBytes(m.parameters.ElementAt(i)));
                    content.Add(temp, $"ParamsList[{i}]");
                }
                client.Timeout = new TimeSpan(0, 0, 600);
               
                var result = await client.PostAsync(uri, content);



                if (result != null && result.IsSuccessStatusCode)
                {
                    var bytes = result.Content.ReadAsByteArrayAsync().Result;
                    return Deal(Encoding.UTF8.GetString(bytes),m.MacId);
                }
                return  "请求超时" ;
            }
            catch (Exception ex)
            {
               
                return "请求超时"+ex;
            }
        }
        /// <summary>
        /// 将机台获取到的参数存放到数据库中
        /// </summary>
        /// <param name="str"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        private object Deal(string str,int id)
        {
            DAParameter sample = new DAParameter();

            SampleRes s = JsonConvert.DeserializeObject<SampleRes>(str);
            if (s.code == "ok")
            {
                //var d = Db.FindObject($@"select id from machine where FCode='{s.data.equipmentID}'");
                sample.MacId = id;
                sample.RecTime = DateTime.Now;
                foreach (var item in s.data.parameters)
                {
                    if (item.paramName == "560")
                    {
                        sample.X = item.paramValue;
                    }
                    if (item.paramName == "561")
                    {
                        sample.Y = item.paramValue;
                    }
                    if (item.paramName == "562")
                    {
                        sample.T = item.paramValue;
                    }
                    if (item.paramName == "1070")
                    {
                        sample.MDP = item.paramValue;
                    }
                    if (item.paramName == "1060")
                    {
                        sample.ADP = item.paramValue;
                    }
                    if (item.paramName == "19")
                    {
                        string type = item.paramValue;
                        type = type.Replace(" ", "-");
                        int i = type.IndexOf("-");
                        sample.Type = type.Substring(0, i);
                    }
                }
                if (Db.InsertFor<DAParameter>(sample, "自动采集") > 0)
                {
                    return "新增成功";
                }
                else
                {
                    return "增加失败";
                }
            }
            return sample;
        }
    }
    
}