public IActionResult Index(QryDocShutVModel qdata)
        {
            string qryDocType    = qdata.qtyDOCTYPE;
            string qryShutStatus = qdata.qtyShutStatus;

            List <RepairListVModel> rv = new List <RepairListVModel>();
            List <KeepListVModel>   kv = new List <KeepListVModel>();

            ViewData["SHUTSTATUS"] = qryShutStatus;
            if (qryDocType == "請修")
            {
                rv = GetRepairShutList(qdata);
                return(PartialView("RepList", rv));
            }
            else
            {
                kv = GetKeepShutList(qdata);
                return(PartialView("KeepList", kv));
            }
        }
        public List <RepairListVModel> GetRepairShutList(QryDocShutVModel qdata)
        {
            string   ticketno         = qdata.qtyTICKETNO;
            string   docid            = qdata.qtyDOCID;
            string   qryDocType       = qdata.qtyDOCTYPE;
            string   qryShutStatus    = qdata.qtyShutStatus;
            string   qtyShutDate      = qdata.qtyShutDate;
            DateTime?qtySendDateFrom  = qdata.qtySendDateFrom;
            DateTime?qtySendDateTo    = qdata.qtySendDateTo;
            DateTime?qtyApplyDateFrom = qdata.qtyApplyDateFrom;
            DateTime?qtyApplyDateTo   = qdata.qtyApplyDateTo;
            DateTime?qtyEndDateFrom   = qdata.qtyEndDateFrom;
            DateTime?qtyEndDateTo     = qdata.qtyEndDateTo;

            if (!string.IsNullOrEmpty(docid))
            {
                docid = docid.Trim();
            }
            if (!string.IsNullOrEmpty(ticketno))
            {
                ticketno = ticketno.ToUpper();
            }

            var ts                     = _context.Tickets.AsQueryable();
            var repairCost             = _context.RepairCosts.Include(r => r.TicketDtl).AsQueryable();
            var repair                 = _context.Repairs.AsQueryable();
            List <RepairListVModel> rv = new List <RepairListVModel>();

            if (!string.IsNullOrEmpty(ticketno))    //發票號碼
            {
                repairCost = repairCost.Where(rc => rc.TicketDtl.TicketDtlNo.ToUpper() == ticketno);
            }
            if (!string.IsNullOrEmpty(docid))   //表單編號
            {
                repair = repair.Where(r => r.DocId == docid);
            }
            if (qtyApplyDateFrom != null || qtyApplyDateTo != null)   //完帳日(發票作帳日)
            {
                if (qtyApplyDateFrom != null && qtyApplyDateTo != null)
                {
                    qtyApplyDateTo = qtyApplyDateTo.Value.AddDays(1).AddSeconds(-1);
                    ts             = ts.Where(t => t.ApplyDate.Value >= qtyApplyDateFrom && t.ApplyDate.Value <= qtyApplyDateTo);
                }
                else
                {
                    var qDate = qtyApplyDateFrom.HasValue == true ? qtyApplyDateFrom.Value : qtyApplyDateTo.Value;
                    ts = ts.Where(t => t.ApplyDate.Value.Date == qDate.Date);
                }
                repairCost = repairCost.Where(rc => rc.StockType == "2" || rc.StockType == "4")
                             .Join(ts, rc => rc.TicketDtl.TicketDtlNo, t => t.TicketNo,
                                   (rc, t) => rc);
            }

            var query = repair.Join(repairCost.Select(rc => rc.DocId).Distinct(),
                                    r => r.DocId, rc => rc, (r, rc) => r)
                        .Join(_context.RepairDtls, r => r.DocId, d => d.DocId,
                              (r, d) => new
            {
                repair = r,
                repdtl = d
            })
                        .Join(_context.Departments, j => j.repair.AccDpt, d => d.DptId,
                              (j, d) => new
            {
                repair = j.repair,
                repdtl = j.repdtl,
                dpt    = d
            })
                        .Where(r => r.repdtl.CloseDate != null); //篩選已結案的表單

            if (!string.IsNullOrEmpty(qryShutStatus))            //是否已經關帳
            {
                if (qryShutStatus == "已關帳")
                {
                    query = query.Where(q => q.repdtl.ShutDate != null);
                }
                else
                {
                    query = query.Where(q => q.repdtl.ShutDate == null);
                }
            }
            if (!string.IsNullOrEmpty(qtyShutDate)) //關帳年月
            {
                int      year     = Convert.ToInt32(qtyShutDate.Substring(0, 3)) + 1911;
                int      month    = Convert.ToInt32(qtyShutDate.Substring(3, 2));
                DateTime dateFrom = new DateTime(year, month, 1);
                DateTime dateTo   = dateFrom.AddMonths(1).AddSeconds(-1);
                query = query.Where(q => q.repdtl.ShutDate >= dateFrom && q.repdtl.ShutDate <= dateTo);
            }
            if (qtySendDateFrom != null || qtySendDateTo != null)   //申請日
            {
                if (qtySendDateFrom != null && qtySendDateTo != null)
                {
                    qtySendDateTo = qtySendDateTo.Value.AddDays(1).AddSeconds(-1);
                    query         = query.Where(q => q.repair.ApplyDate >= qtySendDateFrom && q.repair.ApplyDate <= qtySendDateTo);
                }
                else
                {
                    var qDate = qtySendDateFrom.HasValue == true ? qtySendDateFrom.Value : qtySendDateTo.Value;
                    query = query.Where(q => q.repair.ApplyDate.Date == qDate.Date);
                }
            }
            if (qtyEndDateFrom != null || qtyEndDateTo != null) //完工日
            {
                if (qtyEndDateFrom != null && qtyEndDateTo != null)
                {
                    qtyEndDateTo = qtyEndDateTo.Value.AddDays(1).AddSeconds(-1);
                    query        = query.Where(q => q.repdtl.EndDate.Value >= qtyEndDateFrom && q.repdtl.EndDate.Value <= qtyEndDateTo);
                }
                else
                {
                    var qDate = qtyEndDateFrom.HasValue == true ? qtyEndDateFrom.Value : qtyEndDateTo.Value;
                    query = query.Where(q => q.repdtl.EndDate.Value.Date == qDate.Date);
                }
            }

            query.ToList()
            .ForEach(j => rv.Add(new RepairListVModel
            {
                DocType    = "請修",
                RepType    = j.repair.RepType,
                DocId      = j.repair.DocId,
                ApplyDate  = j.repair.ApplyDate,
                AssetNo    = j.repair.AssetNo,
                AssetName  = j.repair.AssetName,
                PlaceLoc   = j.repair.LocType,
                ApplyDpt   = j.repair.DptId,
                AccDpt     = j.repair.AccDpt,
                AccDptName = j.dpt.Name_C,
                TroubleDes = j.repair.TroubleDes,
                DealState  = _context.DealStatuses.Find(j.repdtl.DealState).Title,
                DealDes    = j.repdtl.DealDes,
                Cost       = j.repdtl.Cost,
                Days       = DateTime.Now.Subtract(j.repair.ApplyDate).Days,
                EndDate    = j.repdtl.EndDate,
                CloseDate  = j.repdtl.CloseDate,
                IsCharged  = j.repdtl.IsCharged,
                repdata    = j.repair
            }));

            return(rv);
        }
        public List <KeepListVModel> GetKeepShutList(QryDocShutVModel qdata)
        {
            string   ticketno         = qdata.qtyTICKETNO;
            string   docid            = qdata.qtyDOCID;
            string   qryDocType       = qdata.qtyDOCTYPE;
            string   qtyShutDate      = qdata.qtyShutDate;
            string   qryShutStatus    = qdata.qtyShutStatus;
            DateTime?qtySendDateFrom  = qdata.qtySendDateFrom;
            DateTime?qtySendDateTo    = qdata.qtySendDateTo;
            DateTime?qtyApplyDateFrom = qdata.qtyApplyDateFrom;
            DateTime?qtyApplyDateTo   = qdata.qtyApplyDateTo;
            DateTime?qtyEndDateFrom   = qdata.qtyEndDateFrom;
            DateTime?qtyEndDateTo     = qdata.qtyEndDateTo;

            if (!string.IsNullOrEmpty(docid))
            {
                docid = docid.Trim();
            }
            if (!string.IsNullOrEmpty(ticketno))
            {
                ticketno = ticketno.ToUpper();
            }

            var ts                   = _context.Tickets.AsQueryable();
            var keepCost             = _context.KeepCosts.Include(r => r.TicketDtl).AsQueryable();
            var keep                 = _context.Keeps.AsQueryable();
            List <KeepListVModel> kv = new List <KeepListVModel>();

            if (!string.IsNullOrEmpty(ticketno))    //發票號碼
            {
                keepCost = keepCost.Where(rc => rc.TicketDtl.TicketDtlNo.ToUpper() == ticketno);
            }
            if (!string.IsNullOrEmpty(docid))   //表單編號
            {
                keep = keep.Where(r => r.DocId == docid);
            }
            if (qtyApplyDateFrom != null || qtyApplyDateTo != null)   //完帳日(發票作帳日)
            {
                if (qtyApplyDateFrom != null && qtyApplyDateTo != null)
                {
                    qtyApplyDateTo = qtyApplyDateTo.Value.AddDays(1).AddSeconds(-1);
                    ts             = ts.Where(t => t.ApplyDate.Value >= qtyApplyDateFrom && t.ApplyDate.Value <= qtyApplyDateTo);
                }
                else
                {
                    var qDate = qtyApplyDateFrom.HasValue == true ? qtyApplyDateFrom.Value : qtyApplyDateTo.Value;
                    ts = ts.Where(t => t.ApplyDate.Value.Date == qDate.Date);
                }
                keepCost = keepCost.Where(rc => rc.StockType == "2" || rc.StockType == "4")
                           .Join(ts, rc => rc.TicketDtl.TicketDtlNo, t => t.TicketNo,
                                 (rc, t) => rc);
            }

            var query = keep.Join(keepCost.Select(kc => kc.DocId).Distinct(),
                                  k => k.DocId, kc => kc, (k, kc) => k)
                        .Join(_context.KeepDtls, k => k.DocId, d => d.DocId,
                              (k, d) => new
            {
                keep    = k,
                keepdtl = d
            })
                        .Join(_context.Departments, j => j.keep.AccDpt, d => d.DptId,
                              (j, d) => new
            {
                keep    = j.keep,
                keepdtl = j.keepdtl,
                dpt     = d
            })
                        .Where(k => k.keepdtl.CloseDate != null); //篩選已結案的表單

            if (!string.IsNullOrEmpty(qryShutStatus))             //是否已經關帳
            {
                if (qryShutStatus == "已關帳")
                {
                    query = query.Where(q => q.keepdtl.ShutDate != null);
                }
                else
                {
                    query = query.Where(q => q.keepdtl.ShutDate == null);
                }
            }
            if (!string.IsNullOrEmpty(qtyShutDate)) //關帳年月
            {
                int      year     = Convert.ToInt32(qtyShutDate.Substring(0, 3)) + 1911;
                int      month    = Convert.ToInt32(qtyShutDate.Substring(3, 2));
                DateTime dateFrom = new DateTime(year, month, 1);
                DateTime dateTo   = dateFrom.AddMonths(1).AddSeconds(-1);
                query = query.Where(q => q.keepdtl.ShutDate >= dateFrom && q.keepdtl.ShutDate <= dateTo);
            }
            if (qtySendDateFrom != null || qtySendDateTo != null)   //申請日
            {
                if (qtySendDateFrom != null && qtySendDateTo != null)
                {
                    qtySendDateTo = qtySendDateTo.Value.AddDays(1).AddSeconds(-1);
                    query         = query.Where(q => q.keep.SentDate.Value >= qtySendDateFrom && q.keep.SentDate.Value <= qtySendDateTo);
                }
                else
                {
                    var qDate = qtySendDateFrom.HasValue == true ? qtySendDateFrom.Value : qtySendDateTo.Value;
                    query = query.Where(q => q.keep.SentDate.Value.Date == qDate.Date);
                }
            }
            if (qtyEndDateFrom != null || qtyEndDateTo != null) //完工日
            {
                if (qtyEndDateFrom != null && qtyEndDateTo != null)
                {
                    qtyEndDateTo = qtyEndDateTo.Value.AddDays(1).AddSeconds(-1);
                    query        = query.Where(q => q.keepdtl.EndDate.Value >= qtyEndDateFrom && q.keepdtl.EndDate.Value <= qtyEndDateTo);
                }
                else
                {
                    var qDate = qtyEndDateFrom.HasValue == true ? qtyEndDateFrom.Value : qtyEndDateTo.Value;
                    query = query.Where(q => q.keepdtl.EndDate.Value.Date == qDate.Date);
                }
            }

            query.ToList()
            .ForEach(j => kv.Add(new KeepListVModel
            {
                DocType    = "保養",
                DocId      = j.keep.DocId,
                PlaceLoc   = j.keep.PlaceLoc,
                ApplyDpt   = j.keep.DptId,
                AccDpt     = j.keep.AccDpt,
                AccDptName = j.dpt.Name_C,
                Result     = (j.keepdtl.Result == null || j.keepdtl.Result == 0) ? "" : _context.KeepResults.Find(j.keepdtl.Result).Title,
                InOut      = j.keepdtl.InOut == "0" ? "自行" :
                             j.keepdtl.InOut == "1" ? "委外" :
                             j.keepdtl.InOut == "2" ? "租賃" :
                             j.keepdtl.InOut == "3" ? "保固" : "",
                Memo      = j.keepdtl.Memo,
                Cost      = j.keepdtl.Cost,
                Days      = DateTime.Now.Subtract(j.keep.SentDate.GetValueOrDefault()).Days,
                Src       = j.keep.Src,
                SentDate  = j.keep.SentDate,
                EndDate   = j.keepdtl.EndDate,
                IsCharged = j.keepdtl.IsCharged,
                keepdata  = j.keep
            }));

            return(kv);
        }