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

namespace Siger.Middlelayer.QmsRepository.Repositories
{
    internal class CheckSnTraceDetailRepository : QMSRepositoryBase<siger_check_sn_trace_detail>, ICheckSnTraceDetailRepository
    {
        private readonly ApiQmsDbContext _context;
        public CheckSnTraceDetailRepository(ApiQmsDbContext context) : base(context)
        {
            _context = context;
        }

        public IPagedCollectionResult<ResponseDataCollection> GetPagedList(List<int> sectionid, int productid, int materialid, 
            int route, int checktype, string sn, string result, List<string> traceIds, string starttime, string endtime, int userid, int itemid,
            int projectId, int page, int pagesize, string toexcel)
        {
            var minDate = "0001-01-01 00:00:00".ToDateTime();
            var query = _context.siger_check_sn_trace_inspection.Where(q => q.projectid == projectId && q.status == (int)RowState.Valid &&
                q.inspection_type == (int)InspectionType.ManualCollection);
            var querylist = from q in query
                            join trace in _context.siger_check_sn_trace_detail on q.trace_id equals trace.TraceID into tt
                            from trace in tt.DefaultIfEmpty()
                            join p in _context.siger_project_product on q.productid equals p.id into pp
                            from p in pp.DefaultIfEmpty()
                            join m in _context.siger_tr_materials on q.materialid equals m.id into mm
                            from m in mm.DefaultIfEmpty()
                            join s in _context.siger_project_level_section on q.sectionid equals s.id into ss
                            from s in ss.DefaultIfEmpty()
                            join r in _context.siger_project_beat_set on q.routeid equals r.id into rr
                            from r in rr.DefaultIfEmpty()
                            join u1 in _context.siger_project_user on q.send_mid equals u1.mid into uu1
                            from u1 in uu1.DefaultIfEmpty()
                            join u2 in _context.siger_project_user on q.check_mid equals u2.mid into uu2
                            from u2 in uu2.DefaultIfEmpty()
                            select new ResponseDataCollection
                            {
                                id = q.trace_id,
                                productid = q.productid,
                                productcode = p.code ?? "",
                                productname = p.name ?? "",
                                sectionid = q.sectionid,
                                sectionname = s.title ?? "",
                                workorder = q.workorder,
                                sn = q.sn,
                                result = q.result,
                                check_mid = q.check_mid,
                                check_user = u2.name ?? "",
                                check_time = (q.check_time.HasValue && q.check_time != DateTime.Now) ?
                                    q.check_time.Value.ToString(ParameterConstant.DateTimeFormat) : "",
                                routeid = q.routeid,
                                routename = r.route_name ?? "",
                                materialid = q.materialid,
                                materialname = m.name ?? "",
                                materialpn = m.pn ?? "",
                                checktype = q.check_type,
                                createtime = q.check_time.HasValue ? q.check_time.Value : minDate,
                                quantity = q.quantity,
                                reason = q.reason,
                                itemid = trace == null ? 0 : trace.ItemID
                            };

            Expression<Func<ResponseDataCollection, bool>> materialExpression = q => true;
            if (materialid > 0)
            {
                materialExpression = q => q.materialid == materialid;
            }
            Expression<Func<ResponseDataCollection, bool>> sectionExpression = q => true;
            if (sectionid.Any())
            {
                sectionExpression = q => sectionid.Contains(q.sectionid);
            }
            Expression<Func<ResponseDataCollection, bool>> productExpression = q => true;
            if (productid > 0)
            {
                productExpression = q => q.productid == productid;
            }
            Expression<Func<ResponseDataCollection, bool>> routeExpression = q => true;
            if (route > 0)
            {
                routeExpression = q => q.routeid == route;
            }
            Expression<Func<ResponseDataCollection, bool>> checkTypeExpression = q => true;
            if (checktype > 0)
            {
                checkTypeExpression = q => q.checktype == checktype;
            }
            Expression<Func<ResponseDataCollection, bool>> snExpression = q => true;
            if (!string.IsNullOrEmpty(sn))
            {
                snExpression = q => q.sn == sn;
            }
            Expression<Func<ResponseDataCollection, bool>> resultExpression = q => true;
            if (!string.IsNullOrEmpty(result))
            {
                resultExpression = q => q.result == result;
            }
            Expression<Func<ResponseDataCollection, bool>> traceIdExpression = q => true;
            if (traceIds.Any())
            {
                traceIdExpression = q => traceIds.Contains(q.id);
            }
            Expression<Func<ResponseDataCollection, bool>> userIdExpression = q => true;
            if (userid > 0)
            {
                userIdExpression = q => q.check_mid == userid;
            }
            Expression<Func<ResponseDataCollection, bool>> timeExpression = q => true;
            if (!string.IsNullOrEmpty(starttime) && !string.IsNullOrEmpty(endtime))
            {
                timeExpression = q => q.createtime <= endtime.ToDateTime() && q.createtime >= starttime.ToDateTime();
            }
            Expression<Func<ResponseDataCollection, bool>> itemExpression = q => true;
            if (itemid > 0)
            {
                itemExpression = q => q.itemid == itemid;
            }

            var expression = materialExpression.And(sectionExpression).And(productExpression).And(routeExpression)
                .And(checkTypeExpression).And(snExpression).And(resultExpression).And(traceIdExpression).And(userIdExpression).And(timeExpression).And(itemExpression);
            //var groups = from q in querylist.Where(expression).OrderByDescending(t => t.createtime)
            //             group q by new { q.id } into g
            //             select g.FirstOrDefault();
            if (toexcel.ToInt() > 0)
            {
                var entities = querylist.Where(expression).OrderByDescending(t => t.createtime).ToLookup(p => p.id).Select(coll => coll.FirstOrDefault()).ToList();
                return new PagedCollectionResult<ResponseDataCollection>(entities, entities.Count);
            }
            else
            {
                var dataList = querylist.Where(expression).ToLookup(p => p.id).Select(coll => coll.FirstOrDefault());
                var totalCount = dataList.Count();
                var entities = dataList.OrderByDescending(t => t.createtime).Skip((page - 1) * pagesize).Take(pagesize).ToList();
                return new PagedCollectionResult<ResponseDataCollection>(entities, totalCount);
            }
        }

        public List<double> GetHistoryDts(int limt, string traceId, int typeCode, int projectId, int itemId, DateTime minDate)
        {
            var query = from d in _context.siger_check_sn_trace_detail
                        join t in _context.siger_check_sn_trace_inspection on d.TraceID equals t.trace_id
                        where d.ItemID == itemId && d.TraceID != traceId && d.Value.HasValue && d.CreateTime <= minDate && t.check_type == typeCode &&
                        d.projectid == projectId && d.status == (int)RowState.Valid && t.status == (int)RowState.Valid && t.projectid == projectId

                        select d;

            return query.OrderByDescending(t => t.CreateTime).OrderByDescending(t => t.NumberIndex)
                    .OrderByDescending(t => t.ID).Take(limt).Select(t => t.Value.Value).ToList();
        }

        public IEnumerable<SnTrace> GetSnTraceList(List<int> sectionIds, int productid, int materialid, int checktype, string starttime, string endtime, int projectid)
        {
            var query = from q in _context.siger_check_sn_trace_inspection
                            //join t in _context.siger_check_sn_trace_inspection on q.TraceID equals t.trace_id
                        where q.projectid == productid && q.status == (int)RowState.Valid //&& t.status == (int)RowState.Valid && q.projectid == productid
                        && q.check_status == (int)SendCheckStatus.Completed
                        select new SnTrace
                        {
                            ID = q.trace_id,
                            ProductID = q.productid,
                            MaterialID = q.materialid,
                            SectionID = q.sectionid,
                            WorkOrder = q.workorder,
                            SN = q.sn,
                            Result = q.result == ((int)SendTestType.Unqualified).ToString() ? "NG" : "OK",
                            UserID = q.checking_mid,
                            CreateTime = q.check_time > DateTime.MinValue ? q.check_time.Value : DateTime.MinValue,
                            RouteID = q.routeid,
                            CheckType = q.check_type,
                        };

            Expression<Func<SnTrace, bool>> materialExpression = q => true;
            if (materialid > 0)
            {
                materialExpression = q => q.MaterialID == materialid;
            }
            Expression<Func<SnTrace, bool>> sectionExpression = q => true;
            if (sectionIds.Any())
            {
                sectionExpression = q => sectionIds.Contains(q.SectionID);
            }
            Expression<Func<SnTrace, bool>> productExpression = q => true;
            if (productid > 0)
            {
                productExpression = q => q.ProductID == productid;
            }
            Expression<Func<SnTrace, bool>> checkTypeExpression = q => true;
            if (checktype > 0)
            {
                checkTypeExpression = q => q.CheckType == checktype;
            }
            Expression<Func<SnTrace, bool>> timeExpression = q => true;
            if (!string.IsNullOrEmpty(starttime) && !string.IsNullOrEmpty(endtime))
            {
                timeExpression = q => q.CreateTime <= endtime.ToDateTime() && q.CreateTime >= starttime.ToDateTime();
            }
            var expression = materialExpression.And(sectionExpression).And(productExpression).And(checkTypeExpression).And(timeExpression);

            return query.Where(expression);
        }
    }
}
