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

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

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

        public int InsertTrans(IEnumerable<MaterialErrorMessage> errMsgs)
        {
            return CurrDb.InsertFor<MaterialErrorMessage>(errMsgs, string.Empty);
        }

        public IEnumerable<ErrorMessageDto> GetByRecipe(string filter, string subFilter, int start, int length, string sort, string order, out int total)
        {
            var list = new List<ErrorMessageDto>();
            var rejectDal = new MachineMaterialDetailDal(CurrDb);
            var rejects = rejectDal.GetByRecipe(filter, subFilter, start, length, sort, order, out total);
            if (rejects != null && rejects.Count() > 0)
            {
                var errorMsgsSql = $@"select sum(count) count,category,recipe,type from (select count,case when (category='LPA' or category='Lens') then 'Lens' else 'Sensor' end as category,
                                      recipe,type from (                                
                                      select sum(count) as count,fdate,recipe,substring_index(error,' ',1) as category,type from
                                      materialerrormessage a where 1=1 and (type='Pick&Place' or type='PR') {filter}
                                      group by fdate,recipe,substring_index(error,' ',1),type) tt where (tt.category='Lens' or tt.category='LPA' or
                                      tt.category='SPA' or tt.category='CPA')) h
                                      group by category,recipe,type
                                       ";
                var errmessages = CurrDb.FindList<ErrorMessageCountDto>(errorMsgsSql);
                foreach (var item in rejects)
                {
                    var entity = new ErrorMessageDto
                    {
                        FKey = item.FKey,
                        Output = item.Output,
                        LensInput = item.LenInput,
                        LensVacuoAlarms = errmessages.Where(c => c.Category == "Lens" && c.Recipe == item.FKey && c.Type == "Pick&Place")?.Sum(c => c.Count),
                        LensPrAlarms = errmessages.Where(c => c.Category == "Lens" && c.Recipe == item.FKey && c.Type == "PR")?.Sum(c => c.Count),
                        SensorInput = item.SensorInput,
                        SensorVacuoAlarms = errmessages.Where(c => c.Category == "Sensor" && c.Recipe == item.FKey && c.Type == "Pick&Place")?.Sum(c => c.Count),
                        SensorPrAlarms = errmessages.Where(c => c.Category == "Sensor" && c.Recipe == item.FKey && c.Type == "PR")?.Sum(c => c.Count),
                        LensVacuoAlarmRate = "0",
                        LensPrAlarmRate = "0",
                        SensorVacuoAlarmRate = "0",
                        SensorPrAlarmRate = "0"
                    };
                    entity.LensPrAlarms = entity.LensPrAlarms == null ? 0 : entity.LensPrAlarms;
                    entity.LensVacuoAlarms = entity.LensVacuoAlarms == null ? 0 : entity.LensVacuoAlarms;
                    entity.SensorPrAlarms = entity.SensorPrAlarms == null ? 0 : entity.SensorPrAlarms;
                    entity.SensorVacuoAlarms = entity.SensorVacuoAlarms == null ? 0 : entity.SensorVacuoAlarms;
                    if (item.LenInput > 0)
                    {
                        entity.LensVacuoAlarmRate = Math.Round(Convert.ToDouble(entity.LensVacuoAlarms) / Convert.ToDouble(entity.LensInput),4).ToString();
                       
                        entity.LensPrAlarmRate = Math.Round(Convert.ToDouble(entity.LensPrAlarms) / Convert.ToDouble(entity.LensInput),4).ToString();
                    }
                    if (entity.SensorInput > 0)
                    {
                        entity.SensorVacuoAlarmRate = Math.Round(Convert.ToDouble(entity.SensorVacuoAlarms) / Convert.ToDouble(entity.SensorInput),4).ToString();
                        entity.SensorPrAlarmRate = Math.Round(Convert.ToDouble(entity.SensorPrAlarms) / Convert.ToDouble(entity.SensorInput),4).ToString();
                    }
                    list.Add(entity);
                }
            }
            return list;
        }

        public IEnumerable<ErrorMessageDto> GetByMachine(string filter, string subFilter, int start, int length, string sort, string order, out int total)
        {
            var list = new List<ErrorMessageDto>();
            var rejectDal = new MachineMaterialDetailDal(CurrDb);
            var rejects = rejectDal.Get(filter, subFilter, start, length, sort, order, out total);
            if (rejects != null && rejects.Count() > 0)
            {
                var errorMsgsSql = $@"select sum(count) count,b.FCode as Fkey,category,type from (select sum(count) count,macid,case when (category='LPA' or category='Lens') then 'Lens' else 'Sensor' end as category,
                                      type from (                                
                                      select sum(count) as count,macid,substring_index(error,' ',1) as category,type from
                                      materialerrormessage a  where 1=1 and (type='Pick&Place' or type='PR') {filter}
                                      group by macid,substring_index(error,' ',1),type) t where (t.category='Lens' or t.category='LPA' or
                                      t.category='SPA' or t.category='CPA') group by macid,category,type) tt 
                                      left join machine b on tt.macid=b.id 
                                      where 1=1 {subFilter} group by b.FCode,category,type";
                var errmessages = CurrDb.FindList<ErrorMessageCountDto>(errorMsgsSql);
                foreach (var item in rejects)
                {
                    var entity = new ErrorMessageDto
                    {
                        FKey = item.FKey,
                        Output = item.Output,
                        LensInput = item.LenInput,
                        LensVacuoAlarms = errmessages.Where(c => c.FKey == item.FKey && c.Category == "Lens" && c.Type == "Pick&Place")?.Sum(c => c.Count),
                        LensPrAlarms = errmessages.Where(c => c.FKey == item.FKey && c.Category == "Lens" && c.Type == "PR")?.Sum(c => c.Count),
                        SensorInput = item.SensorInput,
                        SensorVacuoAlarms = errmessages.Where(c => c.FKey == item.FKey && c.Category == "Sensor" && c.Type == "Pick&Place")?.Sum(c => c.Count),
                        SensorPrAlarms = errmessages.Where(c => c.FKey == item.FKey && c.Category == "Sensor" && c.Type == "PR")?.Sum(c => c.Count),
                        LensVacuoAlarmRate = "0",
                        LensPrAlarmRate = "0",
                        SensorVacuoAlarmRate = "0",
                        SensorPrAlarmRate = "0"
                    };
                    entity.LensPrAlarms = entity.LensPrAlarms == null ? 0 : entity.LensPrAlarms;
                    entity.LensVacuoAlarms = entity.LensVacuoAlarms == null ? 0 : entity.LensVacuoAlarms;
                    entity.SensorPrAlarms = entity.SensorPrAlarms == null ? 0 : entity.SensorPrAlarms;
                    entity.SensorVacuoAlarms = entity.SensorVacuoAlarms == null ? 0 : entity.SensorVacuoAlarms;
                    if (item.LenInput > 0)
                    {
                        entity.LensVacuoAlarmRate = Math.Round(Convert.ToDouble(entity.LensVacuoAlarms) / Convert.ToDouble(entity.LensInput),4).ToString();
                        entity.LensPrAlarmRate = Math.Round(Convert.ToDouble(entity.LensPrAlarms) / Convert.ToDouble(entity.LensInput),4).ToString();
                    }
                    if (entity.SensorInput > 0)
                    {
                        entity.SensorVacuoAlarmRate =Math.Round( Convert.ToDouble(entity.SensorVacuoAlarms) / Convert.ToDouble(entity.SensorInput),4).ToString();
                        entity.SensorPrAlarmRate =Math.Round( Convert.ToDouble(entity.SensorPrAlarms) / Convert.ToDouble(entity.SensorInput),4).ToString();
                    }
                    list.Add(entity);
                }
            }
            return list;
        }

        public IEnumerable<MaterialErrorMessage> GetMaterialErrorMessages(string filter, string subFilter)
        {
            string errorinfo = string.Empty;
            var sql = $@"select sum(TotalIdleTime) as TotalIdleTime,sum(count) as count,Type,Error from
                         (select sum(totalIdleTime) as TotalIdleTime,Type,sum(count) as Count,error,macid 
                         from materialerrormessage a
                         where 1=1 {filter}
                         group by error,type,macid) tt
                         left join machine c on tt.macid=c.id
                         where 1=1 {subFilter}
                         group by Type,Error";
            var entities = CurrDb.FindList<MaterialErrorMessage>(sql);
            return entities;
        }

        public ErrorMessageDto GetTotalInfo(string filter, string subFilter)
        {
            var errMsgDto = new ErrorMessageDto();
            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 ";
            var reject = CurrDb.FindList<MaterialRejectDto>(sql).FirstOrDefault();
            sql = $@"select sum(count) count,category,type from (
                     select count,case when (category='LPA' or category='Lens') then 'Lens' else 'Sensor' end as category,
                     type,macid from (                                
                     select sum(count) as count,fdate,macid,substring_index(error,' ',1) as category,type from
                     materialerrormessage a  where 1=1 and (type='Pick&Place' or type='PR') {filter}
                     group by fdate,macid,substring_index(error,' ',1),type) t where (t.category='Lens' or t.category='LPA' or
                     t.category='SPA' or t.category='CPA') ) tt 
                     left join machine b on tt.macid=b.id 
                     where 1=1 {subFilter}
                     group by category,type";
            var errMsgs = CurrDb.FindList<ErrorMessageCountDto>(sql);
            if (reject != null)
            {
                errMsgDto.FKey = reject.FKey;
                errMsgDto.Output = reject.Output;
                errMsgDto.LensInput = reject.LenInput;
                errMsgDto.LensVacuoAlarms = errMsgs.Where(c => c.Category == "Lens" && c.Type == "Pick&Place")?.Sum(c => c.Count);
                errMsgDto.LensPrAlarms = errMsgs.Where(c => c.Category == "Lens" && c.Type == "PR")?.Sum(c => c.Count);
                errMsgDto.SensorInput = reject.SensorInput;
                errMsgDto.SensorVacuoAlarms = errMsgs.Where(c => c.Category == "Sensor" && c.Type == "Pick&Place")?.Sum(c => c.Count);
                errMsgDto.SensorPrAlarms = errMsgs.Where(c => c.Category == "Sensor" && c.Type == "PR")?.Sum(c => c.Count);
                errMsgDto.LensPrAlarms = errMsgDto.LensPrAlarms == null ? 0 : errMsgDto.LensPrAlarms;
                errMsgDto.LensVacuoAlarms = errMsgDto.LensVacuoAlarms == null ? 0 : errMsgDto.LensVacuoAlarms;
                errMsgDto.SensorPrAlarms = errMsgDto.SensorPrAlarms == null ? 0 : errMsgDto.SensorPrAlarms;
                errMsgDto.SensorVacuoAlarms = errMsgDto.SensorVacuoAlarms == null ? 0 : errMsgDto.SensorVacuoAlarms;
                if (errMsgDto.LensInput > 0)
                {
                    errMsgDto.LensVacuoAlarmRate = Math.Round(Convert.ToDouble(errMsgDto.LensVacuoAlarms) / Convert.ToDouble(errMsgDto.LensInput)).ToString();
                    errMsgDto.LensPrAlarmRate = Math.Round(Convert.ToDouble(errMsgDto.LensPrAlarms) / Convert.ToDouble(errMsgDto.LensInput),4).ToString();
                }
                if (errMsgDto.SensorInput > 0)
                {
                    errMsgDto.SensorVacuoAlarmRate =Math.Round( Convert.ToDouble(errMsgDto.SensorVacuoAlarms) / Convert.ToDouble(errMsgDto.SensorInput),4).ToString();
                    errMsgDto.SensorPrAlarmRate = Math.Round(Convert.ToDouble(errMsgDto.SensorPrAlarms) / Convert.ToDouble(errMsgDto.SensorInput),4).ToString();
                }
            }
            return errMsgDto;
        }
    }
}