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

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

        public IPagedCollectionResult<ResponseSendTestCollection> GetPagedList(List<int> sectionid, int productid, int routeid, int materialid, int checktype, 
            string testroom, string result, int checkstatus, string sn, string starttime, string endtime, 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.SendTest);
            var querylist = from q in query
                            join p in _context.siger_project_product on q.productid equals p.id
                            join s in _context.siger_project_level_section on q.sectionid equals s.id
                            join m in _context.siger_tr_materials on q.materialid equals m.id into mm
                            from m in mm.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_user on q.send_mid equals u1.id into uu1
                            from u1 in uu1.DefaultIfEmpty()
                            join u2 in _context.siger_user on q.check_mid equals u2.id into uu2
                            from u2 in uu2.DefaultIfEmpty()
                            join u3 in _context.siger_user on q.recieve_mid equals u3.id into uu3
                            from u3 in uu3.DefaultIfEmpty()
                            join u4 in _context.siger_user on q.checking_mid equals u4.id into uu4
                            from u4 in uu4.DefaultIfEmpty()
                            select new ResponseSendTestCollection
                            {
                                id = q.id,
                                traceid = q.trace_id,
                                productid = q.productid,
                                productcode = p.code ?? "",
                                productname = p.name ?? "",
                                materialid = q.materialid,
                                materialpn = m.pn ?? "",
                                materialname = m.name ?? "",
                                materialspec = m.spec ?? "",
                                sectionid = q.sectionid,
                                sectionname = s.title ?? "",
                                routeid = q.routeid,
                                routename = r.route_name ?? "",
                                testroom = q.testroom,
                                sn = q.sn,
                                checktype = q.check_type,
                                checkstatus = q.check_status,
                                result = q.result,
                                send_mid = q.send_mid,
                                send_user = u1.nickname ?? "",
                                send_datetime = q.send_time,
                                send_time = q.send_time.ToString(ParameterConstant.DateTimeFormat),
                                check_mid = q.check_mid,
                                check_user = u2.nickname ?? "",
                                check_datetime = q.check_time.HasValue ? q.check_time.Value : minDate,
                                check_time = (q.check_time.HasValue && q.check_time != DateTime.MinValue) ?
                                    q.check_time.Value.ToString(ParameterConstant.DateTimeFormat) : "",
                                number = q.number,
                                reason = q.reason,
                                quantity = q.quantity,
                                recieve_mid = q.recieve_mid,
                                recieve_user = u3.nickname ?? "",
                                recieve_time = (q.recieve_time.HasValue && q.recieve_time != DateTime.MinValue) ?
                                    q.recieve_time.Value.ToString(ParameterConstant.DateTimeFormat) : "",
                                checking_mid = q.checking_mid,
                                checking_user = u4.nickname ?? "",
                                checking_time = (q.checking_time.HasValue && q.checking_time != DateTime.MinValue) ?
                                    q.checking_time.Value.ToString(ParameterConstant.DateTimeFormat) : "",
                            };

            Expression<Func<ResponseSendTestCollection, bool>> sectionExpression = q => true;
            if (sectionid.Any())
            {
                sectionExpression = q => sectionid.Contains(q.sectionid);
            }
            Expression<Func<ResponseSendTestCollection, bool>> productExpression = q => true;
            if (productid > 0)
            {
                productExpression = q => q.productid == productid;
            }
            Expression<Func<ResponseSendTestCollection, bool>> routeExpression = q => true;
            if (routeid > 0)
            {
                routeExpression = q => q.routeid == routeid;
            }
            Expression<Func<ResponseSendTestCollection, bool>> materialExpression = q => true;
            if (materialid > 0)
            {
                materialExpression = q => q.materialid == materialid;
            }
            Expression<Func<ResponseSendTestCollection, bool>> checktypeExpression = q => true;
            if (checktype > 0)
            {
                checktypeExpression = q => q.checktype == checktype;
            }
            Expression<Func<ResponseSendTestCollection, bool>> testroomExpression = q => true;
            if (!string.IsNullOrEmpty(testroom))
            {
                testroomExpression = q => q.testroom == testroom;
            }
            Expression<Func<ResponseSendTestCollection, bool>> resultExpression = q => true;
            if (!string.IsNullOrEmpty(result))
            {
                resultExpression = q => q.result.ToUpper() == result;
            }
            Expression<Func<ResponseSendTestCollection, bool>> checkstatusExpression = q => true;
            if (checkstatus > 0)
            {
                checktypeExpression = q => q.checkstatus == checkstatus;
            }
            Expression<Func<ResponseSendTestCollection, bool>> snExpression = q => true;
            if (!string.IsNullOrEmpty(sn))
            {
                snExpression = q => q.sn == sn;
            }
            Expression<Func<ResponseSendTestCollection, bool>> timeExpression = q => true;
            if (!string.IsNullOrEmpty(starttime) && !string.IsNullOrEmpty(endtime))
            {
                var stime = starttime.ToDateTime();
                var etime = endtime.ToDateTime();
                timeExpression = q => (q.check_datetime <= etime && q.check_datetime >= stime || q.send_datetime >= stime && q.send_datetime <= etime);
            }
            var expression = sectionExpression.And(productExpression).And(routeExpression).And(materialExpression).And(checktypeExpression).And(testroomExpression)
                .And(resultExpression).And(checkstatusExpression).And(snExpression).And(timeExpression);
            var totalCount = querylist.Count(expression);
            if(toexcel.ToInt() == 1)
            {
                var entities = querylist.Where(expression).OrderByDescending(t => t.id).ToList();
                return new PagedCollectionResult<ResponseSendTestCollection>(entities, totalCount);
            }
            else
            {
                var entities = querylist.Where(expression).OrderByDescending(t => t.id)
                    .Skip((page - 1) * pagesize).Take(pagesize).AsNoTracking().ToList();
                return new PagedCollectionResult<ResponseSendTestCollection>(entities, totalCount);
            }
        }

        public IEnumerable<siger_check_testroom> GetTestRoomSections(int projectid)
        {
            var query = _context.siger_check_testroom.Where(t => t.projectid == projectid && t.status == (int)RowState.Valid);
            return query;
        }

        public IEnumerable<siger_check_sn_trace_detail> GetMonitorTraceDetails(int sectionid, int productid, int itemid, int checktype
            ,string starttime, string endtime, int projectid)
        {
            var query = _context.siger_check_sn_trace_detail.Where(t => t.projectid == projectid && t.status == (int)RowState.Valid);
            var queryList = from q in query
                            join s in _context.siger_check_sn_trace_inspection on q.TraceID equals s.trace_id
                            where s.sectionid == sectionid && s.check_type == checktype && s.productid == productid &&
                                q.ItemID == itemid && s.projectid == projectid && s.status == (int)RowState.Valid
                            select q;
            if(!string.IsNullOrEmpty(starttime) && !string.IsNullOrEmpty(endtime))
            {
                var stime = starttime.ToDateTime();
                var etime = endtime.ToDateTime();
                queryList = from q in query
                            join s in _context.siger_check_sn_trace_inspection on q.TraceID equals s.trace_id
                            where s.sectionid == sectionid && s.check_type == checktype && s.productid == productid &&
                                q.ItemID == itemid && s.projectid == projectid && s.status == (int)RowState.Valid &&
                                s.check_time <= etime && s.check_time >= stime
                            select q;
            }
            return queryList;
        }

        public IEnumerable<siger_tr_sn_trace_detail> GetSnTraceDetails(int sectionid, string productid, string itemid
           , string starttime, string endtime, int projectid)
        {
            var query = _context.siger_tr_sn_trace_detail.Where(t => t.projectid == projectid && t.status == (int)RowState.Valid);

            var queryList = from p in query
                            join q in _context.siger_tr_sn_trace on p.SeqID equals q.SeqID
                            where q.Station == sectionid && q.ProductID == productid && p.Item3 == itemid &&
                            q.status == (int)RowState.Valid && q.projectid == projectid
                            select p;
            if(!string.IsNullOrEmpty(starttime) && !string.IsNullOrEmpty(endtime))
            {
                var stime = starttime.ToDateTime();
                var etime = endtime.ToDateTime();
                queryList = from p in query
                            join q in _context.siger_tr_sn_trace on p.SeqID equals q.SeqID
                            where q.Station == sectionid && q.ProductID == productid && p.Item3 == itemid &&
                            p.TransDateTime >= stime && p.LastUpdateTime <= etime &&
                            q.status == (int)RowState.Valid && q.projectid == projectid
                            select p;
            }
            return queryList;
        }
        /// <summary>
        /// 获取过程检，送检数据
        /// </summary>
        /// <param name="project"></param>
        /// <param name="begin"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public IEnumerable<QmsMetarialData> GetChkInspectionData(int project, DateTime begin, DateTime end)
        {
            var query = from m in _context.siger_check_sn_trace_inspection
                        join l in _context.siger_project_level_section on m.sectionid equals l.id
                        join p in _context.siger_project_level_section on l.parentid equals p.id
                        join u in _context.siger_project_user on m.send_mid equals u.mid

                        where m.projectid == project && m.send_time >= begin && m.send_time <= end
                        select new QmsMetarialData
                        {
                            Section = m.sectionid,
                            SectionDesc = p.title,
                            UserId = m.send_mid,
                            UserName = u.name,
                            InspChkType=m.inspection_type,
                            CheckStatus=m.check_status
                        };

            return query;

            throw new NotImplementedException();
        }
    }
}
