using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore;
using Siger.Middlelayer.Common;
using Siger.Middlelayer.Common.Extensions;
using Siger.Middlelayer.Common.Helpers;
using Siger.Middlelayer.Common.ModuleEnum;
using Siger.Middlelayer.Log;
using Siger.Middlelayer.QmsRepository.Entities;
using Siger.Middlelayer.QmsRepository.Repositories.Interface;
using Siger.Middlelayer.QmsRepository.Response;
using Siger.Middlelayer.Repository.Entities;
using Siger.Middlelayer.Repository.Extensions;
using Siger.Middlelayer.Repository.Paged;
using Siger.Middlelayer.Utility.Helpers;
using Siger.Middlelayer.Utility.ImportEntities;

namespace Siger.Middlelayer.QmsRepository.Repositories
{
    internal class InspectStandardRepository : QMSRepositoryBase<siger_qms_inspection_standard>, IInspectStandardRepository
    {
        private readonly ApiQmsDbContext _context;
        public InspectStandardRepository(ApiQmsDbContext context) : base(context)
        {
            _context = context;
        }
        public IPagedCollectionResult<RepsonseInspectStandard> GetPagedList(int productid, List<int> sectionid, int projectId, int page, int pagesize)
        {
            var rowstate = (int)RowState.Valid;
            var query = _context.siger_qms_inspection_standard.Where(q => q.projectid == projectId && q.status == rowstate);
            var querylist = from q in query
                            join se1 in _context.siger_project_level_section on q.sectionid equals se1.id into section1
                            from se1 in section1.DefaultIfEmpty()
                            join p in _context.siger_project_product on q.productid equals p.id into product
                            from p in product.DefaultIfEmpty()
                            join u in _context.siger_user on q.update_mid equals u.id into user
                            from u in user.DefaultIfEmpty()
                            join unhealthy in _context.siger_qms_inspection_unhealthy on q.unhealthy_id equals unhealthy.id into un
                            from unhealthy in un.DefaultIfEmpty()
                            join category in _context.siger_qms_inspection_unhealthy_category on unhealthy.categoryid equals category.id into c
                            from category in c.DefaultIfEmpty()
                            select new RepsonseInspectStandard
                            {
                                id = q.id,
                                productid = q.productid,
                                productcode = q.productcode,
                                productname = p.name,
                                sectionid = q.sectionid,
                                section_value = se1.title ?? "",
                                seq = q.seq,
                                item = q.item,
                                standard = q.standard,
                                unit = q.unit,
                                max_value = q.max_value,
                                min_value = q.min_value,
                                unhealthy_code = unhealthy.code ?? "",
                                unhealthy_desc = q.unhealthy_desc,
                                update_mid = u.nickname ?? "",
                                update_time = UnixTimeHelper.ConvertIntDateTime(q.update_time),
                                value_type = q.value_type,
                                unhealthy_id = q.unhealthy_id,
                                unhealthy_name = unhealthy.name ?? "",
                                unhealthy_category_id = unhealthy != null ? unhealthy.categoryid : 0,
                                unhealthy_category_name = category.name ?? "",
                                value_category = q.value_category,
                                range = q.range,
                                standard_type = q.standard_type,
                                check_type = q.checktype,
                                ucl = q.ucl,
                                lcl = q.lcl,
                                image=q.image,
                                trigger_andon = q.trigger_andon
                            };

            Expression<Func<RepsonseInspectStandard, bool>> productExpression = q => true;
            if (productid > 0)
            {
                productExpression = q => q.productid == productid;
            }
            Expression<Func<RepsonseInspectStandard, bool>> sectionExpression = q => true;
            if (sectionid.Any())
            {
                sectionExpression = q => sectionid.Contains(q.sectionid);
            }
            var expression = productExpression.And(sectionExpression);
            var totalCount = querylist.GroupBy(t => t.id).Select(t => t.FirstOrDefault()).Count(expression);
            var entities = querylist.Where(expression).OrderByDescending(t => t.update_time).GroupBy(t => t.id).Select(t => t.FirstOrDefault())
                .Skip((page - 1) * pagesize).Take(pagesize).AsNoTracking().ToList();
            return new PagedCollectionResult<RepsonseInspectStandard>(entities, totalCount);
        }

        public IEnumerable<RepsonseInspectStandard> GetDataList(int productid, List<int> sectionid, int projectId)
        {
            var rowstate = (int)RowState.Valid;
            var query = _context.siger_qms_inspection_standard.Where(q => q.projectid == projectId && q.status == rowstate);
            var querylist = from q in query
                            join se1 in _context.siger_project_level_section on q.sectionid equals se1.id into section1
                            from se1 in section1.DefaultIfEmpty()
                            join p in _context.siger_project_product on q.productid equals p.id into product
                            from p in product.DefaultIfEmpty()
                            join u in _context.siger_user on q.update_mid equals u.id into user
                            from u in user.DefaultIfEmpty()
                            join unhealthy in _context.siger_qms_inspection_unhealthy on q.unhealthy_id equals unhealthy.id into un
                            from unhealthy in un.DefaultIfEmpty()
                            join category in _context.siger_qms_inspection_unhealthy_category on unhealthy.categoryid equals category.id into c
                            from category in c.DefaultIfEmpty()
                            select new RepsonseInspectStandard
                            {
                                id = q.id,
                                productid = q.productid,
                                productcode = q.productcode,
                                productname = p.name,
                                sectionid = q.sectionid,
                                section_value = se1.title ?? "",
                                seq = q.seq,
                                item = q.item,
                                standard = q.standard,
                                unit = q.unit,
                                max_value = q.max_value,
                                min_value = q.min_value,
                                unhealthy_code = unhealthy.code,
                                unhealthy_desc = unhealthy.name,
                                update_mid = u.nickname ?? "",
                                update_time = UnixTimeHelper.ConvertIntDateTime(q.update_time),
                                value_type = q.value_type,
                                unhealthy_id = q.unhealthy_id,
                                unhealthy_name = unhealthy.name ?? "",
                                unhealthy_category_id = unhealthy != null ? unhealthy.categoryid : 0,
                                unhealthy_category_name = category.name ?? "",
                                value_category = q.value_category,
                                range = q.range,
                                standard_type = q.standard_type,
                                check_type = q.checktype,
                                ucl = q.ucl,
                                lcl = q.lcl,
                                trigger_andon = q.trigger_andon
                            };

            Expression<Func<RepsonseInspectStandard, bool>> productExpression = q => true;
            if (productid > 0)
            {
                productExpression = q => q.productid == productid;
            }
            Expression<Func<RepsonseInspectStandard, bool>> sectionExpression = q => true;
            if (sectionid.Any())
            {
                sectionExpression = q => sectionid.Contains(q.sectionid);
            }

            var expression = productExpression.And(sectionExpression);
            var entities = querylist.Where(expression).OrderByDescending(t => t.update_time).GroupBy(t => t.id)
                .Select(t => t.FirstOrDefault()).AsNoTracking().ToList();
            return entities;
        }

        public CommonImportResult ImportInspectionStandard(IEnumerable<InspectionStandardLevelTemplate> standards, int levelCount, int projectid, int userid,
            Dictionary<string, int> categories)
        {
            try
            {
                var errors = new List<string>();
                var rowIndex = 1;         
                var standardList = new List<InspectionStandards>();
                var products = _context.siger_project_product.Where(t => t.projectid == projectid && t.status == (int)RowState.Valid).ToList();
                var levelSections = _context.siger_project_level_section.Where(t => t.projectid == projectid && t.status == (int)RowState.Valid).ToList();
                foreach (var item in standards)
                {
                    rowIndex++;
                    var model = Mapper<InspectionStandardLevelTemplate, InspectionStandards>.Map(item);
                    var product = products.FirstOrDefault(t => t.code == item.productcode);
                    if(product == null)
                    {
                        errors.Add($"{rowIndex},{(int)RequestEnum.ProductNotFound}");
                        return new CommonImportResult(0, string.Join(";", errors));
                    }
                    if (item.sections == null || !item.sections.Any() || item.sections.Count != levelCount)
                    {
                        errors.Add($"{rowIndex},{(int)RequestEnum.LineError}");
                        return new CommonImportResult(0, string.Join(";", errors));
                    }

                    var pids = new List<int> { 0 };
                    var section = new siger_project_level_section();
                    foreach (var sec in item.sections)
                    {
                        var secs = levelSections.Where(t => t.title == sec && pids.Contains(t.parentid)).ToList();
                        if (!secs.Any())
                        {
                            errors.Add($"{rowIndex},{(int)RequestEnum.LineError}");
                            return new CommonImportResult(0, string.Join(";", errors));
                        }
                        else
                        {
                            pids = secs.Select(t => t.id).ToList();
                        }

                        if (sec == item.sections.Last())
                        {
                            section = secs.FirstOrDefault();
                            if(pids.Count != 1)
                            {
                                errors.Add($"{rowIndex},{(int)RequestEnum.LineError}");
                                return new CommonImportResult(0, string.Join(";", errors));
                            }
                        }
                    }
                    if(section == null)
                    {
                        errors.Add($"{rowIndex},{(int)RequestEnum.PositionError}");
                        return new CommonImportResult(0, string.Join(";", errors));
                    }
                    var unhealthy = _context.siger_qms_inspection_unhealthy.FirstOrDefault(t =>
                            t.code == item.unhealthy_code && t.status == (int)RowState.Valid && t.projectid == projectid);
                    if (unhealthy == null)
                    {
                        errors.Add($"{rowIndex},{(int)RequestEnum.UnhealthyCodeNotFound}");
                        return new CommonImportResult(0, string.Join(";", errors));
                    }

                    var itemPinYin = PinYinHelper.ToPinYin(item.item);
                    if (string.IsNullOrWhiteSpace(itemPinYin))
                    {
                        itemPinYin = item.item;
                    }
                    if (string.IsNullOrEmpty(itemPinYin) || string.IsNullOrEmpty(item.item))
                    {
                        errors.Add($"{rowIndex},{(int)RequestEnum.InspectItemRepeat}");
                    }

                    model.productid = product.id;
                    model.sectionid = section.id;
                    model.unhealthyid = unhealthy.id;
                    model.unhealthyname = unhealthy.name;
                    model.item_en = itemPinYin;

                    if(string.IsNullOrEmpty(item.max_value) && !string.IsNullOrEmpty(item.min_value))
                    {
                        model.max_value = QmsLimitValue.MaxValue;
                    }
                    if (!string.IsNullOrEmpty(item.max_value) && string.IsNullOrEmpty(item.min_value))
                    {
                        model.min_value = QmsLimitValue.MinValue;
                    }

                    standardList.Add(model);
                }
                rowIndex = 1;
                var newList = new List<InspectionStandards>();
                foreach (var p in standardList)
                {
                    rowIndex++;                                              
                    if (p.value_type != "2")
                    {
                        p.max_value = "";
                        p.min_value = "";
                    }
                    if (p.value_type == "2" && (string.IsNullOrEmpty(p.min_value) || string.IsNullOrEmpty(p.max_value) ||
                        p.min_value.ToDouble() > p.max_value.ToDouble()))
                    {
                        errors.Add($"{rowIndex},{(int)RequestEnum.MaxMinError}");
                    }

                    if ((p.value_category.ToInt() == (int)ValueCategory.Maxmin && string.IsNullOrEmpty(p.range)) || p.seq <= 0 ||
                        (p.value_type.ToInt() == (int)ValueTypeStatus.V && p.value_category.ToInt() <= 0) || 
                        string.IsNullOrWhiteSpace(p.lcl) && !string.IsNullOrWhiteSpace(p.ucl) || string.IsNullOrWhiteSpace(p.lcl) && !string.IsNullOrWhiteSpace(p.ucl))
                    {
                        errors.Add($"{rowIndex},{(int)RequestEnum.ParameterMiss}");
                    }

                    var queryItem = _context.siger_qms_inspection_standard.FirstOrDefault(t => (t.item == p.item || t.item_en == p.item_en) &&
                        t.projectid == projectid && t.status == (int)RowState.Valid && t.productid == p.productid && t.sectionid == p.sectionid);
                    if(queryItem != null || standardList.Count(t => (t.item == p.item || t.item_en == p.item_en) && 
                        t.productid == p.productid && t.sectionid == p.sectionid) > 1 && p.value_type!="3")
                    {
                        errors.Add($"{rowIndex},{(int)RequestEnum.InspectItemRepeat}");
                    }

                    var querySeq = _context.siger_qms_inspection_standard.FirstOrDefault(t => t.seq == p.seq &&
                       t.projectid == projectid && t.status == (int)RowState.Valid && t.productid == p.productid &&
                       t.sectionid == p.sectionid && t.standard_type == p.isinpect.ToInt());
                    if (querySeq != null || standardList.Count(t => t.seq == p.seq &&
                         t.productid == p.productid && t.sectionid == p.sectionid && t.isinpect == p.isinpect) > 1 && p.value_type!="3")
                    {
                        errors.Add($"{rowIndex},{(int)RequestEnum.ItemSeqExsit}");
                    }

                    if(!string.IsNullOrWhiteSpace(p.ucl) && !string.IsNullOrWhiteSpace(p.lcl) && p.ucl.ToDouble() < p.lcl.ToDouble())
                    {
                        errors.Add($"{rowIndex},{(int)RequestEnum.UpperLowerError}");
                    }
                    newList.Add(p);
                }
                if (errors.Any())
                {
                    return new CommonImportResult(0, string.Join(";", errors));
                }

                var entities = new List<siger_qms_inspection_standard>();
                foreach (var p in newList)
                {
                    if (p.value_type == "3")
                    {
                        var standard = _context.siger_qms_inspection_standard.Where(q => q.value_type == 3 && q.productcode == p.productcode && q.sectionid == p.sectionid && q.seq == p.seq).FirstOrDefault();
                        if (standard == null)
                        {
                            var data = entities.Where(q => q.value_type == 3 && q.productcode == p.productcode && q.sectionid == p.sectionid && q.seq == p.seq).FirstOrDefault();
                            if (data != null)
                            {
                                continue;
                            }
                        }
                            var psEntity = new siger_qms_inspection_standard
                            {
                                productid = p.productid,
                                productcode = p.productcode,
                                sectionid = p.sectionid,
                                standard = p.standard,
                                seq = p.seq,
                                item = p.item ?? "",
                                item_en = p.item_en,
                                unit = p.unit ?? "",
                                max_value = Math.Round(p.max_value.ToDouble(), 4),
                                min_value = Math.Round(p.min_value.ToDouble(), 4),
                                unhealthy_code = p.unhealthy_code,
                                unhealthy_desc = p.unhealthyname,
                                value_type = p.value_type.ToInt(),
                                create_mid = userid,
                                create_time = UnixTimeHelper.GetNow(),
                                update_mid = userid,
                                update_time = UnixTimeHelper.GetNow(),
                                projectid = projectid,
                                status = (int)RowState.Valid,
                                unhealthy_id = p.unhealthyid,
                                range = p.range.ToDouble(),
                                value_category = p.value_category.ToInt(),
                                standard_type = p.isinpect.ToInt(),
                                checktype = GetCheckTypes(p.inpectcategories, categories),
                                lcl = p.value_type.ToInt() == (int)ValueTypeStatus.V ? p.lcl.ToDouble() : (double?)null,
                                ucl = p.value_type.ToInt() == (int)ValueTypeStatus.V ? p.ucl.ToDouble() : (double?)null,
                                trigger_andon = p.trigger_andon.ToInt()
                            };
                            entities.Add(psEntity);
                        
                    }
                    else
                    {
                        var psEntity = new siger_qms_inspection_standard
                        {
                            productid = p.productid,
                            productcode = p.productcode,
                            sectionid = p.sectionid,
                            standard = p.standard,
                            seq = p.seq,
                            item = p.item ?? "",
                            item_en = p.item_en,
                            unit = p.unit ?? "",
                            max_value = Math.Round(p.max_value.ToDouble(), 4),
                            min_value = Math.Round(p.min_value.ToDouble(), 4),
                            unhealthy_code = p.unhealthy_code,
                            unhealthy_desc = p.unhealthyname,
                            value_type = p.value_type.ToInt(),
                            create_mid = userid,
                            create_time = UnixTimeHelper.GetNow(),
                            update_mid = userid,
                            update_time = UnixTimeHelper.GetNow(),
                            projectid = projectid,
                            status = (int)RowState.Valid,
                            unhealthy_id = p.unhealthyid,
                            range = p.range.ToDouble(),
                            value_category = p.value_category.ToInt(),
                            standard_type = p.isinpect.ToInt(),
                            checktype = GetCheckTypes(p.inpectcategories, categories),
                            lcl = p.value_type.ToInt() == (int)ValueTypeStatus.V ? p.lcl.ToDouble() : (double?)null,
                            ucl = p.value_type.ToInt() == (int)ValueTypeStatus.V ? p.ucl.ToDouble() : (double?)null,
                            trigger_andon = p.trigger_andon.ToInt()
                        };
                        entities.Add(psEntity);
                    }
                }           
                _context.siger_qms_inspection_standard.AddRange(entities);
                _context.SaveChanges();
                var entity = new List<siger_qms_inspection_standard_option>();
                foreach (var p in newList)
                {
                    if (p.value_type=="3")
                    {
                        var standard = _context.siger_qms_inspection_standard.Where(q => q.value_type == 3 && q.productcode == p.productcode && q.sectionid == p.sectionid && q.seq == p.seq && q.status==(int)RowState.Valid).FirstOrDefault();
                        var option = new siger_qms_inspection_standard_option
                        {
                            standardid= standard.id,
                            option=p.option,
                            option_type=p.result.ToInt(),
                            projectid=projectid,
                            optionValue=3
                        };
                        entity.Add(option);
                    }
                }
                _context.siger_qms_inspection_standard_option.AddRange(entity);
                _context.SaveChanges();
                return new CommonImportResult(1, "1");
            }
            catch(Exception ex)
            {
                Logger.WriteLineError(ex.ToString());
                throw;
            }
        }

        private string GetCheckTypes(string inpectcategories, Dictionary<string, int> dicts)
        {
            var types = new List<int>();
            var categories = inpectcategories.Split(new char[]{';'}, StringSplitOptions.RemoveEmptyEntries);
            foreach (var category in categories)
            {
                var result = dicts.TryGetValue(category, out var value);
                if (result)
                {
                    types.Add(value);
                }
            }

            return string.Join(";", types);
        }

        public RepsonseInspectStandard GetData(int id, int projectId)
        {
            var rowstate = (int)RowState.Valid;
            var query = _context.siger_qms_inspection_standard.Where(q => q.projectid == projectId && q.status == rowstate && q.id == id);
            var querylist = from q in query
                            join se1 in _context.siger_project_level_section on q.sectionid equals se1.id into section1
                            from se1 in section1.DefaultIfEmpty()
                            join p in _context.siger_project_product on q.productid equals p.id into product
                            from p in product.DefaultIfEmpty()
                            join u in _context.siger_user on q.update_mid equals u.id into user
                            from u in user.DefaultIfEmpty()
                            join unhealthy in _context.siger_qms_inspection_unhealthy on q.unhealthy_id equals unhealthy.id into un
                            from unhealthy in un.DefaultIfEmpty()
                            join category in _context.siger_qms_inspection_unhealthy_category on unhealthy.categoryid equals category.id into c
                            from category in c.DefaultIfEmpty()
                            orderby q.update_time descending
                            select new RepsonseInspectStandard
                            {
                                id = q.id,
                                productid = q.productid,
                                productcode = q.productcode,
                                productname = p.name,
                                sectionid = q.sectionid,
                                section_value = se1.title ?? "",
                                seq = q.seq,
                                item = q.item,
                                standard = q.standard,
                                unit = q.unit,
                                max_value = q.max_value,
                                min_value = q.min_value,
                                unhealthy_code = unhealthy.code,
                                unhealthy_desc = unhealthy.name,
                                update_mid = u.nickname ?? "",
                                update_time = UnixTimeHelper.ConvertIntDateTime(q.update_time),
                                value_type = q.value_type,
                                unhealthy_id = q.unhealthy_id,
                                unhealthy_name = unhealthy.name ?? "",
                                unhealthy_category_id = unhealthy != null ? unhealthy.categoryid : 0,
                                unhealthy_category_name = category.name ?? "",
                                value_category = q.value_category,
                                range = q.range,
                                standard_type = q.standard_type,
                                check_type = q.checktype,
                                ucl = q.ucl,
                                lcl = q.lcl,
                                trigger_andon = q.trigger_andon
                            };
            return querylist.FirstOrDefault();
        }


        public IPagedCollectionResult<ResponseTraceDts> GetTraceDtsByItemPagedList(string productCode, int projectId, List<int> itemIds, DateTime begin, DateTime end, int page, int pagesize)
        {
            Expression<Func<ResponseTraceDts, bool>> productExpression = q => true;
            if (!string.IsNullOrEmpty(productCode))
                productExpression = q => q.ProductCode == productCode;
            Expression<Func<ResponseTraceDts, bool>> itemsExpression = q => true;
            if (itemIds.Any())
                itemsExpression = q => itemIds.Contains(q.Item3);
            var expression = productExpression.And(itemsExpression);

            var querylist = from h in _context.siger_tr_sn_trace
                        join d in _context.siger_tr_sn_trace_detail on h.SeqID equals d.SeqID
                        join s in _context.siger_project_level_section on h.Station equals s.id
                        where h.projectid == projectId && h.TransDateTime>=begin && h.TransDateTime<=end
                        select new ResponseTraceDts
                        {
                            id=h.id,
                            SeqId = h.SeqID,
                            Section = h.Station,
                            SectionTitle = s.title,
                            Sn = h.SN,
                            ProductCode = h.ProductID,
                            Result = h.Result,
                            Item = d.Item,
                            Item3 = d.Item3.ToInt(),
                            Value = d.Value
                        };
            var totalCount = querylist.Where(expression).GroupBy(t => t.Sn).Select(t => t.FirstOrDefault()).Count();
            var entities = querylist.Where(expression).GroupBy(t => t.Sn).Select(t => t.FirstOrDefault())
                .Skip((page - 1) * pagesize).Take(pagesize).AsNoTracking().ToList();
            return new PagedCollectionResult<ResponseTraceDts>(entities, totalCount);

        }

        public IEnumerable<ResponseTraceDts> GetTraceDtsByItem(string productCode, int projectId,List<string>snArry, List<int> itemIds, DateTime begin, DateTime end)
        {
            Expression<Func<ResponseTraceDts, bool>> productExpression = q => true;
            if (!string.IsNullOrEmpty(productCode))
                productExpression = q => q.ProductCode == productCode;
            Expression<Func<ResponseTraceDts, bool>> itemsExpression = q => true;
            if (itemIds.Any())
                itemsExpression = q => itemIds.Contains(q.Item3);
            var expression = productExpression.And(itemsExpression);

            var querylist = from h in _context.siger_tr_sn_trace
                            join d in _context.siger_tr_sn_trace_detail on h.SeqID equals d.SeqID
                            join s in _context.siger_project_level_section on h.Station equals s.id
                            where h.projectid == projectId &&
                            snArry.Contains(h.SN) &&
                            h.TransDateTime >= begin && h.TransDateTime <= end
                            select new ResponseTraceDts
                            {
                                id=h.id,
                                SeqId = h.SeqID,
                                Section = h.Station,
                                SectionTitle = s.title,
                                Sn = h.SN,
                                ProductCode = h.ProductID,
                                Result = h.Result,
                                Item = d.Item,
                                Item3 = d.Item3.ToInt(),
                                Value=d.Value
                            };

            return querylist.OrderBy(d=>d.id).Where(expression).AsNoTracking().ToList();
           

        }

        public IEnumerable<ResponseBiheadRoute> GetSectionItem(int projectId,int yItem, List<int> itemIds)
        {
            var query = from s in _context.siger_project_level_section
                        join q in _context.siger_qms_inspection_standard on s.id equals q.sectionid
                        where q.projectid == projectId && itemIds.Contains(q.id)
                        select new ResponseBiheadRoute
                        {
                             sort= q.id==yItem?1:0,
                             ItemId=q.id,
                             Item=q.item,
                             Section=s.id,
                             SectionTitle=s.title
                        };
            return query;
        }

        public IEnumerable<ResponseIdItem> GetSectionValueTypeItem(int projectId, int productId, List<int> stations, int valueType = 0)
        {


            throw new NotImplementedException();
        }

        public IEnumerable<ResponseTraceDts> GetTraceDtsValByItem(string productCode, int projectId, List<int> itemIds, DateTime begin, DateTime end)
        {
            Expression<Func<ResponseTraceDts, bool>> productExpression = q => true;
            if (!string.IsNullOrEmpty(productCode))
                productExpression = q => q.ProductCode == productCode;
            Expression<Func<ResponseTraceDts, bool>> itemsExpression = q => true;
            if (itemIds.Any())
                itemsExpression = q => itemIds.Contains(q.Item3);
            var expression = productExpression.And(itemsExpression);

            var querylist = from h in _context.siger_tr_sn_trace
                            join d in _context.siger_tr_sn_trace_detail on h.SeqID equals d.SeqID
                            join s in _context.siger_project_level_section on h.Station equals s.id
                            where h.projectid == projectId &&
                            h.TransDateTime >= begin && h.TransDateTime <= end
                            select new ResponseTraceDts
                            {
                                id = h.id,
                                SeqId = h.SeqID,
                                Section = h.Station,
                                SectionTitle = s.title,
                                Sn = h.SN,
                                ProductCode = h.ProductID,
                                Result = h.Result,
                                Item = d.Item,
                                Item3 = d.Item3.ToInt(),
                                Value = d.Value
                            };

            return querylist.Where(expression).AsNoTracking();
        }
    }
}

