everlight.eap/backend/DllEapDal/OFILM/MachineMaterialDetailDal.cs

using Cksoft.Data;
using DllEapEntity.OFILM;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DllEapDal.OFILM
{
    public class MachineMaterialDetailDal
    {
        public IDatabase CurrDb { get; set; }
        public MachineMaterialDetailDal(IDatabase db)
        {
            CurrDb = db;
        }

        public int InsertTrans(IEnumerable<MachineMaterialDetail> details)
        {
            if (details == null || details.Count() == 0)
                return -1;
            return CurrDb.InsertFor<MachineMaterialDetail>(details, string.Empty);
        }

        /// <summary>
        /// 按机台别获取特定机种的抛料率统计
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="subFilter"></param>
        /// <param name="start"></param>
        /// <param name="length"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public IEnumerable<MaterialRejectDto> Get(string filter, string subFilter, int start, int length, string sort, string order, out int total)
        {
            var sql = $@"select l.maccode as Fkey,l.LenInput,l.LenReject,l.SensorInput,l.SensorReject,round(l.LenReject/l.LenInput,4) as LenRejectPercent,
                       round(l.SensorReject/l.SensorInput,4) as SensorRejectPercent,l.Output from(
                       select b.fcode as maccode,sum(tt.LenInput) as LenInput,sum(tt.LenInput-tt.Output-tt.LenMissingReject) as LenReject,
                       sum(tt.SensorInput) as SensorInput,sum(tt.SensorInput-tt.output-tt.SensorMissingReject) as SensorReject,
                       sum(tt.output) as Output from(
                       select a.macid as macid,a.fdate,a.shift,a.recipe,max(case a.category when 'Lens Input' then ifnull(total,0) else 0 end) LenInput,
                       max(case a.category when 'Lens(#Missing)'  then  ifnull(total,0) else 0 end) LenMissingReject,
                       max(case a.category when 'Sensor Input' then ifnull(total,0) else 0 end) SensorInput,
                       max(case a.category when 'Sensor(#Missing)' then ifnull(total,0) else 0 end) SensorMissingReject,
                       max(case a.category when 'Camera Output' then ifnull(total,0) else 0 end) output 
                       from machinematerialdetail a
                       where 1=1 {filter}
                       group by a.macid,a.fdate,a.shift,a.recipe
                       ) tt
                       left join machine b on tt.macid=b.id
                       left join factoryregion c on c.id=b.regionId 
                       left join factoryregion d on c.parentId=d.id 
                       where 1=1 {subFilter}
                       group by b.fcode) l order by {sort} {order} limit {start - 1},{length}";
            var dtos = CurrDb.FindList<MaterialRejectDto>(sql);
            var countSql = $@"select count(1) from(
                       select b.fcode as maccode,sum(tt.LenInput) as LenInput,sum(tt.LenInput-tt.Output-tt.LenMissingReject) as LenReject,
                       sum(tt.SensorInput) as SensorInput,sum(tt.SensorInput-tt.output-tt.SensorMissingReject) as SensorReject,
                       sum(tt.output) as Output from(
                       select a.macid as macid,a.fdate,a.shift,a.recipe,max(case a.category when 'Lens Input' then ifnull(total,0) else 0 end) LenInput,
                       max(case a.category when 'Lens(#Missing)'  then  ifnull(total,0) else 0 end) LenMissingReject,
                       max(case a.category when 'Sensor Input' then ifnull(total,0) else 0 end) SensorInput,
                       max(case a.category when 'Sensor(#Missing)' then ifnull(total,0) else 0 end) SensorMissingReject,
                       max(case a.category when 'Camera Output' then ifnull(total,0) else 0 end) output 
                       from machinematerialdetail a
                       where 1=1 {filter}
                       group by a.macid,a.fdate,a.shift,a.recipe
                       ) tt
                       left join machine b on tt.macid=b.id 
                       left join factoryregion c on c.id=b.regionId 
                       left join factoryregion d on c.parentId=d.id 
                       where 1=1 {subFilter}
                       group by b.fcode) l";
            total = Convert.ToInt32(CurrDb.FindList<string>(countSql).FirstOrDefault() ?? "0");
            return dtos;
        }

        /// <summary>
        /// 按机种别获取抛料率统计
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="subFilter"></param>
        /// <param name="start"></param>
        /// <param name="length"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public IEnumerable<MaterialRejectDto> GetByRecipe(string filter, string subFilter, int start, int length, string sort, string order, out int total)
        {
            var sql = $@"select l.recipe as Fkey,l.LenInput,l.LenReject,l.SensorInput,l.SensorReject,round(l.LenReject/l.LenInput,4) as LenRejectPercent,
                       round(l.SensorReject/l.SensorInput,4) as SensorRejectPercent,l.Output from(
                       select tt.recipe as recipe,sum(tt.LenInput) as LenInput,sum(tt.LenInput-tt.Output-tt.LenMissingReject) as LenReject,
                       sum(tt.SensorInput) as SensorInput,sum(tt.SensorInput-tt.output-tt.SensorMissingReject) as SensorReject,
                       sum(tt.output) as Output from(
                       select a.macid as macid,a.fdate,a.shift,a.recipe,max(case a.category when 'Lens Input' then ifnull(total,0) else 0 end) LenInput,
                       max(case a.category when 'Lens(#Missing)'  then  ifnull(total,0) else 0 end) LenMissingReject,
                       max(case a.category when 'Sensor Input' then ifnull(total,0) else 0 end) SensorInput,
                       max(case a.category when 'Sensor(#Missing)' then ifnull(total,0) else 0 end) SensorMissingReject,
                       max(case a.category when 'Camera Output' then ifnull(total,0) else 0 end) output 
                       from machinematerialdetail a
                       where 1=1 {filter}
                       group by a.macid,a.fdate,a.shift,a.recipe
                       ) tt group by tt.recipe
                       ) l order by {sort} {order} limit {start - 1},{length}";
            var dtos = CurrDb.FindList<MaterialRejectDto>(sql);
            var countSql = $@"select count(1) from(
                       select tt.recipe as recipe,sum(tt.LenInput) as LenInput,sum(tt.LenInput-tt.Output-tt.LenMissingReject) as LenReject,
                       sum(tt.SensorInput) as SensorInput,sum(tt.SensorInput-tt.output-tt.SensorMissingReject) as SensorReject,
                       sum(tt.output) as Output from(
                       select a.macid as macid,a.fdate,a.shift,a.recipe,max(case a.category when 'Lens Input' then ifnull(total,0) else 0 end) LenInput,
                       max(case a.category when 'Lens(#Missing)'  then  ifnull(total,0) else 0 end) LenMissingReject,
                       max(case a.category when 'Sensor Input' then ifnull(total,0) else 0 end) SensorInput,
                       max(case a.category when 'Sensor(#Missing)' then ifnull(total,0) else 0 end) SensorMissingReject,
                       max(case a.category when 'Camera Output' then ifnull(total,0) else 0 end) output 
                       from machinematerialdetail a
                       where 1=1 {filter}
                       group by a.macid,a.fdate,a.shift,a.recipe
                       ) tt group by tt.recipe
                       ) l";
            total = Convert.ToInt32(CurrDb.FindList<string>(countSql).FirstOrDefault() ?? "0");
            return dtos;
        }

        public IEnumerable<MachineMaterialDetail> GetMaterialDetails(string filter, string subFilter)
        {
            string errorinfo = string.Empty;
            var sql = $@"select category,sum(total) as Total,sum(Stn1)  as Stn1,sum(Stn2) as Stn2,sum(Stn3) as Stn3,sum(Stn4)  as Stn4 from 
                       (select a.macid,category,sum(Total) as Total,sum(Stn1) as Stn1,sum(stn2) as Stn2,sum(stn3) as stn3,sum(stn4) as stn4 
                       from machinematerialdetail a  where 1=1 and 
                       a.category <>'Lens(#Missing)' and a.category<>'Sensor(#Missing)' 
                        {filter} 
					   group by category,macid ) tt
					   left join machine c on tt.macid=c.id 
					   where 1=1 {subFilter} group by category";
            var entities = CurrDb.FindList<MachineMaterialDetail>(sql);
            if (entities == null || entities.Count() <= 0)
                return null;
            var lenInput = entities.Where(c => c.Category.Contains("Lens Input"))?.Sum(t => t.Total);
            var sensorInput = entities.Where(c => c.Category.Contains("Sensor Input"))?.Sum(t => t.Total);
            var cameraOutput = entities.Where(c => c.Category.Contains("Camera Output"))?.Sum(t => t.Total);
            foreach (var item in entities.Where(c => !(c.Category.Contains("Lens Input") ||
            c.Category.Contains("Sensor Input") || c.Category.Contains("Camera Output"))))
            {
                var minus = lenInput;
                if (item.Category.StartsWith("Lens"))
                    minus = lenInput;
                else if (item.Category.StartsWith("Sensor"))
                    minus = sensorInput;
                else if (item.Category.StartsWith("Camera"))
                    minus = cameraOutput;
                item.Reject = (decimal?)Math.Round((double)item.Total / minus.Value * 100, 2, MidpointRounding.AwayFromZero);
            }
            #region 排序
            var list = new List<MachineMaterialDetail>();
            var lens = entities.Where(c => c.Category.StartsWith("Lens"));
            list.AddRange(lens);
            var sensors = entities.Where(c => c.Category.StartsWith("Sensor"));
            list.AddRange(sensors);
            var cameras = entities.Where(c => c.Category.StartsWith("Camera"));
            list.AddRange(cameras);
            #endregion
            return list;
        }

        /// <summary>
        /// 获取汇总数据
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="subFilter"></param>
        /// <returns></returns>
        public MaterialRejectDto GetTotalInfo(string filter, string subFilter)
        {
            var sql = $@"select 'Total' as Fkey,l.LenInput,l.LenReject,l.SensorInput,l.SensorReject,round(l.LenReject/l.LenInput,4) as LenRejectPercent,
                       round(l.SensorReject/l.SensorInput,4) as SensorRejectPercent,l.Output from(
                       select sum(tt.LenInput) as LenInput,sum(tt.LenInput-tt.Output-tt.LenMissingReject) as LenReject,
                       sum(tt.SensorInput) as SensorInput,sum(tt.SensorInput-tt.output-tt.SensorMissingReject) as SensorReject,
                       sum(tt.output) as Output from(
                       select a.macid as macid,a.fdate,a.shift,a.recipe,max(case a.category when 'Lens Input' then ifnull(total,0) else 0 end) LenInput,
                       max(case a.category when 'Lens(#Missing)'  then  ifnull(total,0) else 0 end) LenMissingReject,
                       max(case a.category when 'Sensor Input' then ifnull(total,0) else 0 end) SensorInput,
                       max(case a.category when 'Sensor(#Missing)' then ifnull(total,0) else 0 end) SensorMissingReject,
                       max(case a.category when 'Camera Output' then ifnull(total,0) else 0 end) output 
                       from machinematerialdetail a
                       where 1=1 {filter}
                       group by a.macid,a.fdate,a.shift,a.recipe 
                       ) tt
                       left join machine b on tt.macid=b.id
                       where 1=1 {subFilter}) l ";
            return CurrDb.FindList<MaterialRejectDto>(sql).FirstOrDefault();
        }

        // public IEnumerable
    }
}