using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore;
using Siger.Middlelayer.AccRepository.Entities;
using Siger.Middlelayer.AccRepository.Repositories.Interface;
using Siger.Middlelayer.AccRepository.Request;
using Siger.Middlelayer.AccRepository.Response;
using Siger.Middlelayer.Common;
using Siger.Middlelayer.Common.Extensions;
using Siger.Middlelayer.Repository.Data;
using Siger.Middlelayer.Repository.Extensions;
using Siger.Middlelayer.Repository.Paged;
using Siger.Middlelayer.Utility.Helpers;
using System.Threading.Tasks;
namespace Siger.Middlelayer.AccRepository.Repositories
{
    internal class SnListRepository : AccRepositoryBase<SigerTrSnList>, ISnListRepository
    {
        private ApiAccDbContext accDbContext;

        public SnListRepository(ApiAccDbContext context) : base(context)
        {
            accDbContext = context;
        }

        public IEnumerable<SnList> GetCountList(int line, int station, int projectid, DateTime begin, DateTime end)
        {
            var query = accDbContext.siger_tr_sn_list.Where(t => t.projectId == projectid && t.TransDateTime >= begin && t.TransDateTime <= end);

            var data = from p in query
                       join q in accDbContext.siger_tr_routing_outstation on
                       new { statuss = p.ResultStatus, projectids = projectid, rowstate = (int)RowState.Valid } equals
                       new { statuss = q.ResultStatus, projectids = q.projectId, rowstate = q.status }
                       into dstemp
                       from ds in dstemp.DefaultIfEmpty()
                       select new SnList
                       {
                           sn = p.SN,
                           productid = p.ProductCode,
                           line = p.Line,
                           station = ds.Station > 0 ? ds.Station : 0,
                           wo = p.WO,
                           pn = p.PN,
                           status = p.ResultStatus,
                           eventno = ds.EventNo > 0 ? ds.EventNo : 0
                       };

            Expression<Func<SnList, bool>> FunLine = f => true;
            Expression<Func<SnList, bool>> FunStation = f => true;
            if (station > 0)
                FunStation = f => f.station == station;
            if (line > 0)
                FunLine = f => f.line == line;
            var predicate = FunLine.And(FunStation);

            var entities = data.Where(predicate).AsNoTracking().ToList();

            return entities;
        }

        public IPagedCollectionResult<SnList> GetPagedList(int line, int station, int projectid, int page, int pagesize)
        {
            var query = accDbContext.siger_tr_sn_list.Where(t => t.projectId == projectid);

            var data = from p in query
                       join q in accDbContext.siger_tr_routing_outstation on
                       new { statuss = p.ResultStatus, projectids = projectid, rowstate = (int)RowState.Valid } equals
                       new { statuss = q.ResultStatus, projectids = q.projectId, rowstate = q.status }
                       into dstemp
                       from ds in dstemp.DefaultIfEmpty()
                       join l in accDbContext.siger_project_level_section on
                       new { lineid = p.Line, rowstate = (int)RowState.Valid } equals
                       new { lineid = l.id, rowstate = l.status }
                       into ltemp
                       from l in ltemp.DefaultIfEmpty()
                       join pi in accDbContext.siger_project_product
                        on new { productid = p.ProductCode, projectids = projectid, rowstatus = (int)RowState.Valid }
                        equals new { productid = pi.code, projectids = pi.projectid, rowstatus = pi.status }
                        into producttemp
                       from pi in producttemp.DefaultIfEmpty()
                       select new SnList
                       {
                           sn = p.SN,
                           productid = p.ProductCode,
                           productid_value = pi.code,
                           line = p.Line > 0 ? p.Line : 0,
                           station = ds.Station > 0 ? ds.Station : 0,
                           line_value = l.title,
                           wo = p.WO,
                           pn = p.PN,
                           status = p.ResultStatus,
                           eventno = ds.EventNo > 0 ? ds.EventNo : 0,
                           inputdatetime = p.InputDateTime,
                           transdatetime = p.TransDateTime
                       };

            var datalist = from p in data
                           join s in accDbContext.siger_project_level_section on
                           new { stationid = p.station, rowstate = (int)RowState.Valid } equals
                           new { stationid = s.id, rowstate = s.status }
                           into stemp
                           from s in stemp.DefaultIfEmpty()
                           join d in accDbContext.siger_tr_dict
                            on new { dkeys = p.eventno, dcat = AccDictCostNoProjectId.eventno, rowstatus = (int)RowState.Valid }
                            equals new { dkeys = d.dkey.ToInt(), dcat = d.cat, rowstatus = d.status }
                            into dtemp
                           from d in dtemp.DefaultIfEmpty()
                           select new SnList
                           {
                               sn = p.sn,
                               productid = p.productid,
                               line = p.line,
                               station = s.id > 0 ? s.id : 0,
                               station_value = s.title,
                               line_value = p.line_value,
                               wo = p.wo,
                               pn = p.pn,
                               status = p.status,
                               eventno = p.eventno,
                               eventno_value = d.dval,
                               inputdatetime = p.inputdatetime,
                               transdatetime = p.transdatetime
                           };

            Expression<Func<SnList, bool>> FunLine = f => true;
            Expression<Func<SnList, bool>> FunStation = f => true;
            if (station > 0)
                FunStation = f => f.station == station;
            if (line > 0)
                FunLine = f => f.line == line;
            var predicate = FunLine.And(FunStation);

            var totalCount = datalist.Count(predicate);
            var entities = datalist.Where(predicate).Skip((page - 1) * pagesize).Take(pagesize).AsNoTracking().ToList();
            return new PagedCollectionResult<SnList>(entities, totalCount);
        }

        public IEnumerable<SnList> GetDataList(int line, int station, int projectid)
        {
            var query = accDbContext.siger_tr_sn_list.Where(t => t.projectId == projectid);

            var data = from p in query
                       join q in accDbContext.siger_tr_routing_outstation on
                       new { statuss = p.ResultStatus, projectids = projectid, rowstate = (int)RowState.Valid } equals
                       new { statuss = q.ResultStatus, projectids = q.projectId, rowstate = q.status }
                       into dstemp
                       from ds in dstemp.DefaultIfEmpty()
                       join l in accDbContext.siger_project_level_section on
                       new { lineid = p.Line, rowstate = (int)RowState.Valid } equals
                       new { lineid = l.id, rowstate = l.status }
                       into ltemp
                       from l in ltemp.DefaultIfEmpty()
                       join pi in accDbContext.siger_project_product
                        on new { productid = p.ProductCode, projectids = projectid, rowstatus = (int)RowState.Valid }
                        equals new { productid = pi.code, projectids = pi.projectid, rowstatus = pi.status }
                        into producttemp
                       from pi in producttemp.DefaultIfEmpty()
                       select new SnList
                       {
                           sn = p.SN,
                           productid = p.ProductCode,
                           productid_value = pi.name,
                           line = p.Line > 0 ? p.Line : 0,
                           station = ds.Station > 0 ? ds.Station : 0,
                           line_value = l.title,
                           wo = p.WO,
                           pn = p.PN,
                           status = p.ResultStatus,
                           eventno = ds.EventNo > 0 ? ds.EventNo : 0,
                           inputdatetime = p.InputDateTime,
                           transdatetime = p.TransDateTime
                       };

            var datalist = from p in data
                           join s in accDbContext.siger_project_level_section on
                           new { stationid = p.station, rowstate = (int)RowState.Valid } equals
                           new { stationid = s.id, rowstate = s.status }
                           into stemp
                           from s in stemp.DefaultIfEmpty()
                           join d in accDbContext.siger_tr_dict
                            on new { dkeys = p.eventno, dcat = AccDictCostNoProjectId.eventno, rowstatus = (int)RowState.Valid }
                            equals new { dkeys = d.dkey.ToInt(), dcat = d.cat, rowstatus = d.status }
                            into dtemp
                           from d in dtemp.DefaultIfEmpty()
                           select new SnList
                           {
                               sn = p.sn,
                               productid = p.productid,
                               line = p.line,
                               station = s.id > 0 ? s.id : 0,
                               station_value = s.title,
                               line_value = p.line_value,
                               wo = p.wo,
                               pn = p.pn,
                               status = p.status,
                               eventno = p.eventno,
                               eventno_value = d.dval,
                               inputdatetime = p.inputdatetime,
                               transdatetime = p.transdatetime
                           };

            Expression<Func<SnList, bool>> FunLine = f => true;
            Expression<Func<SnList, bool>> FunStation = f => true;
            if (station > 0)
                FunStation = f => f.station == station;
            if (line > 0)
                FunLine = f => f.line == line;
            var predicate = FunLine.And(FunStation);
            var entities = datalist.Where(predicate).AsNoTracking().ToList();
            return entities;
        }

        public IEnumerable<SigerTrSnList> GetDataBySn(string SN, int projectId)
        {
            var result = accDbContext.siger_tr_sn_list.Where(f => f.SN == SN && f.projectId == projectId && f.status != (int)RowState.Invalid).OrderByDescending(o => o.id);
            return result.ToList();
        }

        public async Task<IPagedCollectionResult<ResponseSNRpt>> SnListRptDatePagedList(string sn, DateTime begin, DateTime end, int projectid, string productId, int line, string pn, int page, int pagesize)
        {
            return await Task.Run(() =>
            {
                Expression<Func<SigerTrSnList, bool>> FunSn = f => true;
                Expression<Func<SigerTrSnList, bool>> FunDate = f => true;
                Expression<Func<SigerTrSnList, bool>> FunProduct = f => true;
                Expression<Func<SigerTrSnList, bool>> FunLine = f => true;
                Expression<Func<SigerTrSnList, bool>> FunStation = f => true;
                Expression<Func<SigerTrSnList, bool>> FunPn = f => true;
                Expression<Func<SigerTrSnList, bool>> FunProjectId = f => f.projectId == projectid;

                if (!string.IsNullOrEmpty(sn))
                {
                    FunSn = f => f.SN.Contains(sn) && f.status == (int)RowState.Valid;
                }
                else
                {
                    FunDate = f => f.TransDateTime >= begin && f.TransDateTime <= end && f.status == (int)RowState.Valid; ;
                }
                if (!string.IsNullOrEmpty(productId))
                    FunProduct = f => f.ProductCode.Contains(productId);
                if (line != 0)
                    FunLine = f => f.Line == line;
                if (!string.IsNullOrEmpty(pn))
                    FunPn = f => f.PN == pn;
                var predicate = FunProjectId.And(FunSn).And(FunDate).And(FunProduct).And(FunLine).And(FunPn);
             
                var queryResult = from m in accDbContext.siger_tr_sn_list.Where(predicate)
                                  join l in accDbContext.siger_project_level_section on m.Line equals l.id into ltemp
                                  from l in ltemp.DefaultIfEmpty()
                                  join u in accDbContext.siger_tr_routing_outstation on m.ResultStatus equals u.ResultStatus into rtemp
                                  from u in rtemp.DefaultIfEmpty()
                                  join s in accDbContext.siger_project_level_section on u.Station equals s.id into lltemp
                                  from s in lltemp.DefaultIfEmpty()
                                  join e in accDbContext.siger_tr_routing_eventno on u.EventNo equals e.EventNo into etemp
                                  from e in etemp.DefaultIfEmpty()
                                  join r in accDbContext.siger_project_product on m.ProductId equals r.id into rr
                                  from r in rr.DefaultIfEmpty()
                                  where e.projectId == u.projectId 
                                  select new ResponseSNRpt
                                  {
                                      LineNo = m.Line,
                                      Line = l.title,
                                      Sn = m.SN,
                                      ProcutId = m.ProductCode,
                                      ProcutName = m.ProductCode,
                                      Pn = m.PN,
                                      WO = m.WO,
                                      ResultStatus = e.Descr,
                                      InputDateTime = m.InputDateTime,
                                      UpdateTime = m.TransDateTime,
                                      Result = e.Descr,
                                      productname=r!=null?r.name:"",
                                      productdraw= r != null ? r.drawingcode:"",
                                      Station = s.title

                                  };
                var totalCount = queryResult.Count();
                var resultList = queryResult.OrderByDescending(o => o.InputDateTime).Skip((page - 1) * pagesize).Take(pagesize).AsNoTracking();
                return new PagedCollectionResult<ResponseSNRpt>(resultList, totalCount);
            });
        }

        public async Task<IPagedCollectionResult<ResponseSNRpt>> SnListRptDatePagedList(RequestQuerySnData condition, int projectid)
        {
            return await Task.Run(() =>
            {
                Expression<Func<SigerTrSnList, bool>> FunSn = f => true;
                Expression<Func<SigerTrSnList, bool>> FunUid = f => true;
                Expression<Func<SigerTrSnList, bool>> FunBox = f => true;
                Expression<Func<SigerTrSnList, bool>> FunDate = f => true;
                Expression<Func<SigerTrSnList, bool>> FunProduct = f => true;
                Expression<Func<SigerTrSnList, bool>> FunLine = f => true;
                Expression<Func<SigerTrSnList, bool>> FunPn = f => true;
                Expression<Func<SigerTrSnList, bool>> FunProjectId = f => f.projectId == projectid;
                Expression<Func<SigerTrSnList, bool>> FunWo = f => f.projectId == projectid;

                if (!string.IsNullOrEmpty(condition.Sn))
                    FunSn = f => f.SN.Contains(condition.Sn) && f.status == (int)RowState.Valid;
                else
                    FunDate = f => f.TransDateTime >= condition.Starttime && f.TransDateTime <= condition.Endtime && f.status == (int)RowState.Valid;
                if (!string.IsNullOrEmpty(condition.ProductId))
                    FunProduct = f => f.ProductCode.Equals(condition.ProductId);
                if (condition.Line != 0)
                    FunLine = f => f.Line == condition.Line;
                if (!string.IsNullOrEmpty(condition.Pn))
                    FunPn = f => f.PN == condition.Pn;
                if (!string.IsNullOrEmpty(condition.OrderNumber))
                    FunWo = f => f.WO == condition.OrderNumber;
                if (condition.Uid != 0)
                {
                    var sns = accDbContext.siger_tr_sn_trace.Where(f => f.projectId.Equals(projectid) && f.status != (int)RowState.Invalid & f.UID.Equals(condition.Uid)).Select(s => s.SN).Distinct().ToList();
                    FunUid = f => sns.Contains(f.SN);
                }
                if (!string.IsNullOrEmpty(condition.OrderNumber))
                {
                    var sns = accDbContext.siger_tr_pack_box_sn_list.Where(f => f.projectId.Equals(projectid) && f.status != (int)RowState.Invalid & f.BoxID.Equals(condition.Box)).Select(s => s.SN).Distinct().ToList();
                    FunBox = f => sns.Contains(f.SN);
                }
                var predicate = FunProjectId.And(FunSn).And(FunDate).And(FunProduct).And(FunLine).And(FunPn).And(FunWo).And(FunUid).And(FunBox);
                var querySN = accDbContext.Set<SigerTrSnList>().Where(predicate);
                var queryResult = from m in accDbContext.siger_tr_sn_list.Where(predicate)
                                  join l in accDbContext.siger_project_level_section on m.Line equals l.id
                                  join u in accDbContext.siger_tr_routing_outstation on m.ResultStatus equals u.ResultStatus
                                  join s in accDbContext.siger_project_level_section on u.Station equals s.id
                                  join e in accDbContext.siger_tr_routing_eventno on u.EventNo equals e.EventNo
                                  where m.projectId == projectid && m.projectId == u.projectId && e.projectId == u.projectId &&
                                  u.status == (int)RowState.Valid && e.status == (int)RowState.Valid
                                  select new ResponseSNRpt
                                  {
                                      id=m.id,
                                      LineNo = m.Line,
                                      Line = l.title,
                                      Sn = m.SN,
                                      ProcutId = m.ProductCode,
                                      Pn = m.PN,
                                      WO = m.WO,
                                      ResultStatus = e.Descr,
                                      InputDateTime = m.InputDateTime,
                                      UpdateTime = m.TransDateTime,
                                      Result = e.Descr,
                                      Station = s.title,
                                      StationNo = s.id
                                  };

                Expression<Func<ResponseSNRpt, bool>> FunStation = f => true;
                Expression<Func<ResponseSNRpt, bool>> FunResult = f => true;
                if (condition.Station != 0)
                    FunStation = f => f.StationNo == condition.Station;
                if (!string.IsNullOrEmpty(condition.Result))
                    FunResult = f => f.Result == condition.Result;

                var queryCondition = FunStation.And(FunResult);
                var totalCount = queryResult.Count(queryCondition);
                var resultList = queryResult.Where(queryCondition).OrderByDescending(o => o.id).Skip((condition.page - 1) * condition.pagesize).Take(condition.pagesize).AsNoTracking();
                var productModel = accDbContext.siger_project_product.Where(f => f.status == (int)RowState.Valid && f.projectid.Equals(projectid));
                var ret = resultList.ToList();
                foreach (var item in ret)
                {
                    var name = productModel.Where(f => f.code.Equals(item.ProcutId)).Select(s => s.name).FirstOrDefault() ?? "";
                    item.ProcutName = name;
                }
                return new PagedCollectionResult<ResponseSNRpt>(ret, totalCount);
            });
        }

        public IEnumerable<siger_tr_assist_materials> GetDataByBatch(string batch,string wo, int projectId)
        {
            var materials = accDbContext.siger_tr_assist_materials.Where(q => q.batch == batch && q.wo==wo && q.projectId == projectId && q.status == (int)RowState.Valid);
            return materials.ToList();
        }

        public IPagedCollectionResult<RequestMaterials> GetDataAssistMaterials(RequestAssistMaterials batch, int projectId)
        {
            var queryResult = from q in accDbContext.siger_tr_assist_materials
                              join e in accDbContext.siger_project_order_type on q.workordertype equals e.code
                            where q.projectId == projectId && q.status == (int)RowState.Valid
                            select new RequestMaterials
                            {
                                id=q.id,
                                wo=q.wo,
                                batch=q.batch,
                                serial = q.pn,
                                transdatetime = q.transdatetime,
                                lastupdatetime = q.lastupdatetime,
                                name = q.name,
                                number = q.number,
                                productcode = q.productcode,
                                workordertype = e.name,
                                woType=e.code,
                                workordername=e.name,
                                specification = q.specification,
                                pn = q.pn,
                                printNumber = q.printCount
                            };
            //foreach (var item in queryResult)
            //{
            //    var plan = accDbContext.siger_project_product_plan.Where(q => q.workordertype.ToString() == item.woType && q.code == item.wo && q.status == (int)RowState.Valid && q.projectId == projectId).FirstOrDefault();
            //    item.productcode = plan!=null? plan.product_code:"";
            //}
            Expression<Func<RequestMaterials, bool>> FunProductcode = f => true;
            Expression<Func<RequestMaterials, bool>> FunWo = f => true;
            Expression<Func<RequestMaterials, bool>> FunWorkordertype = f => true;
            Expression<Func<RequestMaterials, bool>> FunName = f => true;
            Expression<Func<RequestMaterials, bool>> FunPn = f => true;
            if (!string.IsNullOrEmpty(batch.productCode))
                FunProductcode = f => f.productcode == batch.productCode;
            if (!string.IsNullOrEmpty(batch.wo))
                FunWo = f => f.wo.Equals(batch.wo);
            if (!string.IsNullOrEmpty(batch.woType))
                FunWorkordertype = f => f.woType == batch.woType;
            if (!string.IsNullOrEmpty(batch.name))
                FunName = f => f.name.Contains(batch.name);
            if (!string.IsNullOrEmpty(batch.pn))
                FunPn = f => f.pn.StartsWith(batch.pn);
            var queryCondition = FunProductcode.And(FunWo).And(FunWorkordertype).And(FunName).And(FunPn);
            var totalCount = queryResult.Count(queryCondition);
            var resultList = queryResult.Where(queryCondition).OrderByDescending(o => o.id).Skip((batch.page - 1) * batch.pagesize).Take(batch.pagesize).AsNoTracking().Distinct();
            return new PagedCollectionResult<RequestMaterials>(resultList, totalCount);
        }

        public IEnumerable<RequestMaterial> GetDataWorkOrder(int projectId)
        {
            var queryResult = from q in accDbContext.siger_project_order_type
                              where q.projectId == projectId && q.status == (int)RowState.Valid
                              select new RequestMaterial
                              {
                                  id=q.id,
                                  workordertype = q.code,
                                  workordername = q.name,
                              };
            return queryResult.ToList();
        }
        /// <summary>
        /// 获取流转卡
        /// </summary>
        /// <param name="projectId"></param>
        /// <param name="productCode"></param>
        /// <param name="woType">woCode</param>
        /// <param name="wo"></param>
        /// <param name="page"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IPagedCollectionResult<ResponseFlowCard> GetFlowCards(int projectId, string productCode, string woType, string wo, int page, int pageSize)
        {
            Expression<Func<ResponseFlowCard, bool>> FunProductcode = f => true;
            Expression<Func<ResponseFlowCard, bool>> FunWo = f => true;
            Expression<Func<ResponseFlowCard, bool>> FunWoType = f => true;
            if (!string.IsNullOrEmpty(productCode))
            {
                FunProductcode = f => f.ProductCode.Contains(productCode);
            }
            if (!string.IsNullOrEmpty(wo))
            {
                FunWo = f => f.Wo==wo;
            }
            if (!string.IsNullOrEmpty(woType))
            {
                FunWoType = f => f.WoTypeCode.Equals(woType)&& f.WoTypes.Equals(woType) ;
            }

            var queryCondition = FunProductcode.And(FunWo).And(FunWoType);

            var query = from q in accDbContext.siger_tr_sn_list
                        join p in accDbContext.siger_project_product_plan on q.WO equals p.code 
                        join t in accDbContext.siger_project_order_type on p.workordertype equals t.id into ttemp
                        from t in ttemp.DefaultIfEmpty()
                        where q.projectId == projectId 
                        select new ResponseFlowCard
                        {
                            Id = q.id,
                            Wo = q.WO,
                            WoTypeCode = t != null ? t.code:"",
                            WoTypes=q.WoType,
                            WoType = t != null ? t.name:"",
                            PrintTime = q.printCount,
                            ProduceTime=q.InputDateTime,
                            Qty = p.quantity,
                            ProductCode = p.product_code,
                            ProductName=p.product_name,
                            DrawCode=p.draw_number,
                            Sn = q.SN
                        };
            var totalCount = query.Count(queryCondition);
            var resultList = query.Where(queryCondition).OrderByDescending(q=>q.Wo).ThenBy(o => o.Sn).Skip((page - 1) * pageSize).Take(pageSize).AsNoTracking();
            return new PagedCollectionResult<ResponseFlowCard>(resultList, totalCount);
        }

        public IEnumerable<ResponseWoInfo> GetWoInfo(string sn, int projectId)
        {

            var query = from w in accDbContext.siger_tr_sn_list
                        join c in accDbContext.siger_project_product_plan on w.WO equals c.code
                        where w.projectId == projectId && w.status == (int)RowState.Valid && w.SN == sn
                        select new ResponseWoInfo
                        {
                            wo = w.WO,
                            quantity = c.quantity,
                        };

            return query.ToList();
        }
    }
}
