public IEnumerable <GarmentStockReportViewModel> GetStockQuery(string ctg, string unitcode, DateTime?datefrom, DateTime?dateto, int offset)
        {
            DateTime DateFrom = datefrom == null ? new DateTime(1970, 1, 1) : (DateTime)datefrom;
            DateTime DateTo   = dateto == null ? DateTime.Now : (DateTime)dateto;

            var PPAwal = (from a in dbContext.GarmentUnitReceiptNotes
                          join b in dbContext.GarmentUnitReceiptNoteItems on a.Id equals b.URNId
                          join d in dbContext.GarmentDeliveryOrderDetails on b.POId equals d.POId
                          join f in dbContext.GarmentInternalPurchaseOrders on b.POId equals f.Id
                          join e in dbContext.GarmentReceiptCorrectionItems on b.Id equals e.URNItemId into RC
                          from ty in RC.DefaultIfEmpty()
                          join c in dbContext.GarmentUnitExpenditureNoteItems on b.UENItemId equals c.Id into UE
                          from ww in UE.DefaultIfEmpty()
                          join r in dbContext.GarmentUnitExpenditureNotes on ww.UENId equals r.Id into UEN
                          from dd in UEN.DefaultIfEmpty()
                          join epoItem in dbContext.GarmentExternalPurchaseOrderItems on b.POId equals epoItem.POId into EP
                          from epoItem in EP.DefaultIfEmpty()
                          join epo in dbContext.GarmentExternalPurchaseOrders on epoItem.GarmentEPOId equals epo.Id into EPO
                          from epo in EPO.DefaultIfEmpty()
                          where d.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? d.CodeRequirment : ctg) &&
                          a.IsDeleted == false && b.IsDeleted == false
                          //String.IsNullOrEmpty(a.UENNo) ? false : a.UENNo.Contains(unitcode)
                          //|| a.UnitCode == unitcode
                          //a.UENNo.Contains(unitcode) || a.UnitCode == unitcode
                          //a.UnitCode == unitcode || a.UENNo.Contains(unitcode)

                          //&& a.ReceiptDate.AddHours(offset).Date >= DateFrom.Date
                          && a.CreatedUtc.AddHours(offset).Date < DateFrom.Date
                          select new
            {
                ReceiptDate = a.ReceiptDate,
                CodeRequirment = d.CodeRequirment,
                ProductCode = b.ProductCode,
                ProductName = b.ProductName,
                ProductRemark = b.ProductRemark,
                RO = b.RONo,
                Uom = b.UomUnit,
                Buyer = f.BuyerCode,
                PlanPo = b.POSerialNumber,
                NoArticle = f.Article,
                QtyReceipt = b.ReceiptQuantity,
                QtyCorrection = ty.POSerialNumber == null ? 0 : ty.Quantity,
                QtyExpend = ww.POSerialNumber == null ? 0 : ww.Quantity,
                PriceReceipt = b.PricePerDealUnit,
                PriceCorrection = ty.POSerialNumber == null ? 0 : ty.PricePerDealUnit,
                PriceExpend = ww.POSerialNumber == null ? 0 : ww.PricePerDealUnit,
                POId = b.POId,
                URNType = a.URNType,
                UnitCode = a.UnitCode,
                UENNo = a.UENNo,
                UnitSenderCode = dd.UnitSenderCode == null ? "-" : dd.UnitSenderCode,
                UnitRequestName = dd.UnitRequestName == null ? "-" : dd.UnitRequestName,
                ExpenditureTo = dd.ExpenditureTo == null ? "-" : dd.ExpenditureTo,
                PaymentMethod = epo.PaymentMethod == null ? "-": epo.PaymentMethod,
                a.IsDeleted
            });
            var CobaPP = from a in PPAwal
                         //where a.ReceiptDate.AddHours(offset).Date < DateFrom.Date && a.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? a.CodeRequirment : ctg) && a.IsDeleted == false
                         where a.UENNo.Contains((String.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)) || a.UnitCode == (String.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)
                         select a;
            var PPAkhir = from a in dbContext.GarmentUnitReceiptNotes
                          join b in dbContext.GarmentUnitReceiptNoteItems on a.Id equals b.URNId
                          join d in dbContext.GarmentDeliveryOrderDetails on b.POId equals d.POId
                          join f in dbContext.GarmentInternalPurchaseOrders on b.POId equals f.Id
                          //join f in SaldoAwal on b.POId equals f.POID
                          join e in dbContext.GarmentReceiptCorrectionItems on b.Id equals e.URNItemId into RC
                          from ty in RC.DefaultIfEmpty()
                          join c in dbContext.GarmentUnitExpenditureNoteItems on b.UENItemId equals c.Id into UE
                          from ww in UE.DefaultIfEmpty()
                          join r in dbContext.GarmentUnitExpenditureNotes on ww.UENId equals r.Id into UEN
                          from dd in UEN.DefaultIfEmpty()
                          join epoItem in dbContext.GarmentExternalPurchaseOrderItems on b.POId equals epoItem.POId into EP
                          from epoItem in EP.DefaultIfEmpty()
                          join epo in dbContext.GarmentExternalPurchaseOrders on epoItem.GarmentEPOId equals epo.Id into EPO
                          from epo in EPO.DefaultIfEmpty()

                          where d.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? d.CodeRequirment : ctg) &&
                          a.IsDeleted == false && b.IsDeleted == false
                          //String.IsNullOrEmpty(a.UENNo) ? false : a.UENNo.Contains(unitcode)
                          //|| a.UnitCode == unitcode
                          //a.UnitCode == unitcode || a.UENNo.Contains(unitcode)
                          // a.UENNo.Contains(unitcode) || a.UnitCode == unitcode     /*String.IsNullOrEmpty(a.UENNo) ? true :*/
                          && a.CreatedUtc.AddHours(offset).Date >= DateFrom.Date &&
                          a.CreatedUtc.AddHours(offset).Date <= DateTo.Date

                          select new
            {
                ReceiptDate     = a.ReceiptDate,
                CodeRequirment  = d.CodeRequirment,
                ProductCode     = b.ProductCode,
                ProductName     = b.ProductName,
                ProductRemark   = b.ProductRemark,
                RO              = b.RONo,
                Uom             = b.UomUnit,
                Buyer           = f.BuyerCode,
                PlanPo          = b.POSerialNumber,
                NoArticle       = f.Article,
                QtyReceipt      = b.ReceiptQuantity,
                QtyCorrection   = ty.POSerialNumber == null ? 0 : ty.Quantity,
                QtyExpend       = ww.POSerialNumber == null ? 0 : ww.Quantity,
                PriceReceipt    = b.PricePerDealUnit,
                PriceCorrection = ty.POSerialNumber == null ? 0 : ty.PricePerDealUnit,
                PriceExpend     = ww.POSerialNumber == null ? 0 : ww.PricePerDealUnit,
                POId            = b.POId,
                URNType         = a.URNType,
                UnitCode        = a.UnitCode,
                UENNo           = a.UENNo,
                UnitSenderCode  = dd.UnitSenderCode == null ? "-" : dd.UnitSenderCode,
                UnitRequestName = dd.UnitRequestName == null ? "-" : dd.UnitRequestName,
                ExpenditureTo   = dd.ExpenditureTo == null ? "-" : dd.ExpenditureTo,
                PaymentMethod   = epo.PaymentMethod == null ? "-" : epo.PaymentMethod,
                a.IsDeleted
            };
            var CobaPPAkhir = from a in PPAkhir
                              where a.UENNo.Contains((String.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)) || a.UnitCode == (String.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)
                              //where a.ReceiptDate.AddHours(offset).Date >= DateFrom.Date
                              //      && a.ReceiptDate.AddHours(offset).Date <= DateTo.Date
                              //      && a.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? a.CodeRequirment : ctg)
                              //      && a.IsDeleted == false
                              select a;
            var SaldoAwal = from query in CobaPP
                            group query by new { query.ProductCode, query.ProductName, query.RO, query.PlanPo, query.POId, query.UnitCode, query.UnitSenderCode, query.UnitRequestName } into data
                select new GarmentStockReportViewModel
            {
                ProductCode          = data.Key.ProductCode,
                RO                   = data.Key.RO,
                PlanPo               = data.FirstOrDefault().PlanPo,
                NoArticle            = data.FirstOrDefault().NoArticle,
                ProductName          = data.FirstOrDefault().ProductName,
                ProductRemark        = data.FirstOrDefault().ProductRemark,
                Buyer                = data.FirstOrDefault().Buyer,
                BeginningBalanceQty  = data.Sum(x => x.QtyReceipt) + Convert.ToDecimal(data.Sum(x => x.QtyCorrection)) - Convert.ToDecimal(data.Sum(x => x.QtyExpend)),
                BeginningBalanceUom  = data.FirstOrDefault().Uom,
                ReceiptCorrectionQty = 0,
                ReceiptQty           = 0,
                ReceiptUom           = data.FirstOrDefault().Uom,
                ExpendQty            = 0,
                ExpandUom            = data.FirstOrDefault().Uom,
                EndingBalanceQty     = 0,
                EndingUom            = data.FirstOrDefault().Uom,
                POId                 = data.FirstOrDefault().POId,
                PaymentMethod        = data.FirstOrDefault().PaymentMethod
            };
            var SaldoAkhir = from query in CobaPPAkhir
                             group query by new { query.ProductCode, query.ProductName, query.RO, query.PlanPo, query.POId, query.UnitCode, query.UnitSenderCode, query.UnitRequestName } into data
                select new GarmentStockReportViewModel
            {
                ProductCode          = data.Key.ProductCode,
                RO                   = data.Key.RO,
                PlanPo               = data.FirstOrDefault().PlanPo,
                NoArticle            = data.FirstOrDefault().NoArticle,
                ProductName          = data.FirstOrDefault().ProductName,
                ProductRemark        = data.FirstOrDefault().ProductRemark,
                Buyer                = data.FirstOrDefault().Buyer,
                BeginningBalanceQty  = 0,
                BeginningBalanceUom  = data.FirstOrDefault().Uom,
                ReceiptCorrectionQty = 0,
                ReceiptQty           = data.Sum(x => x.QtyReceipt),
                ReceiptUom           = data.FirstOrDefault().Uom,
                ExpendQty            = data.Sum(x => x.QtyExpend),
                ExpandUom            = data.FirstOrDefault().Uom,
                EndingBalanceQty     = Convert.ToDecimal(Convert.ToDouble(data.Sum(x => x.QtyReceipt)) - data.Sum(x => x.QtyExpend)),
                EndingUom            = data.FirstOrDefault().Uom,
                POId                 = data.FirstOrDefault().POId,
                PaymentMethod        = data.FirstOrDefault().PaymentMethod
            };
            List <GarmentStockReportViewModel> Data1 = SaldoAwal.Concat(SaldoAkhir).ToList();
            var Data = (from query in Data1
                        group query by new { query.POId, query.ProductCode, query.RO } into groupdata
                        select new GarmentStockReportViewModel
            {
                ProductCode = groupdata.FirstOrDefault().ProductCode == null ? "-" : groupdata.FirstOrDefault().ProductCode,
                RO = groupdata.FirstOrDefault().RO == null ? "-" : groupdata.FirstOrDefault().RO,
                PlanPo = groupdata.FirstOrDefault().PlanPo == null ? "-" : groupdata.FirstOrDefault().PlanPo,
                NoArticle = groupdata.FirstOrDefault().NoArticle == null ? "-" : groupdata.FirstOrDefault().NoArticle,
                ProductName = groupdata.FirstOrDefault().ProductName == null ? "-" : groupdata.FirstOrDefault().ProductName,
                ProductRemark = groupdata.FirstOrDefault().ProductRemark,
                Buyer = groupdata.FirstOrDefault().Buyer == null ? "-" : groupdata.FirstOrDefault().Buyer,
                BeginningBalanceQty = groupdata.Sum(x => x.BeginningBalanceQty),
                BeginningBalanceUom = groupdata.FirstOrDefault().BeginningBalanceUom,
                ReceiptCorrectionQty = 0,
                ReceiptQty = groupdata.Sum(x => x.ReceiptQty),
                ReceiptUom = groupdata.FirstOrDefault().ReceiptUom,
                ExpendQty = groupdata.Sum(x => x.ExpendQty),
                ExpandUom = groupdata.FirstOrDefault().ExpandUom,
                EndingBalanceQty = Convert.ToDecimal((groupdata.Sum(x => x.BeginningBalanceQty) + groupdata.Sum(x => x.ReceiptQty) + 0) - (Convert.ToDecimal(groupdata.Sum(x => x.ExpendQty)))),
                EndingUom = groupdata.FirstOrDefault().EndingUom,
                PaymentMethod = groupdata.FirstOrDefault().PaymentMethod
            });

            return(Data.AsQueryable());
        }
Beispiel #2
0
        public IEnumerable <AccountingStockReportViewModel> GetStockQuery(string ctg, string unitcode, DateTime?datefrom, DateTime?dateto, int offset)
        {
            DateTime DateFrom = datefrom == null ? new DateTime(1970, 1, 1) : (DateTime)datefrom;
            DateTime DateTo   = dateto == null ? DateTime.Now : (DateTime)dateto;
            var      PPAwal   = (from a in dbContext.GarmentUnitReceiptNotes
                                 join b in dbContext.GarmentUnitReceiptNoteItems on a.Id equals b.URNId
                                 join d in dbContext.GarmentDeliveryOrderDetails on b.POId equals d.POId
                                 join f in dbContext.GarmentInternalPurchaseOrders on b.POId equals f.Id
                                 join e in dbContext.GarmentReceiptCorrectionItems on b.Id equals e.URNItemId into RC
                                 from ty in RC.DefaultIfEmpty()
                                 join c in dbContext.GarmentUnitExpenditureNoteItems on b.UENItemId equals c.Id into UE
                                 from ww in UE.DefaultIfEmpty()
                                 join r in dbContext.GarmentUnitExpenditureNotes on ww.UENId equals r.Id into UEN
                                 from dd in UEN.DefaultIfEmpty()
                                 where d.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? d.CodeRequirment : ctg) &&
                                 a.IsDeleted == false && b.IsDeleted == false
                                 //String.IsNullOrEmpty(a.UENNo) ? false : a.UENNo.Contains(unitcode)
                                 //|| a.UnitCode == unitcode
                                 //a.UENNo.Contains(unitcode) || a.UnitCode == unitcode
                                 //a.UnitCode == unitcode || a.UENNo.Contains(unitcode)

                                 //&& a.ReceiptDate.AddHours(offset).Date >= DateFrom.Date
                                 && a.CreatedUtc.AddHours(offset).Date < DateFrom.Date
                                 select new
            {
                ReceiptDate = a.ReceiptDate,
                CodeRequirment = d.CodeRequirment,
                ProductCode = b.ProductCode,
                ProductName = b.ProductName,
                RO = b.RONo,
                Uom = b.UomUnit,
                Buyer = f.BuyerCode,
                PlanPo = b.POSerialNumber,
                NoArticle = f.Article,
                QtyReceipt = b.ReceiptQuantity,
                QtyCorrection = ty.POSerialNumber == null ? 0 : ty.Quantity,
                QtyExpend = ww.POSerialNumber == null ? 0 : ww.Quantity,
                PriceReceipt = b.PricePerDealUnit,
                PriceCorrection = ty.POSerialNumber == null ? 0 : ty.PricePerDealUnit,
                PriceExpend = ww.POSerialNumber == null ? 0 : ww.PricePerDealUnit,
                POId = b.POId,
                URNType = a.URNType,
                UnitCode = a.UnitCode,
                UENNo = a.UENNo,
                UnitSenderCode = dd.UnitSenderCode == null ? "-" : dd.UnitSenderCode,
                UnitRequestName = dd.UnitRequestName == null ? "-" : dd.UnitRequestName,
                ExpenditureTo = dd.ExpenditureTo == null ? "-" : dd.ExpenditureTo,
                a.IsDeleted
            });
            var CobaPP = from a in PPAwal
                         //where a.ReceiptDate.AddHours(offset).Date < DateFrom.Date && a.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? a.CodeRequirment : ctg) && a.IsDeleted == false
                         where a.UENNo.Contains((String.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)) || a.UnitCode == (String.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)
                         select a;
            var PPAkhir = from a in dbContext.GarmentUnitReceiptNotes
                          join b in dbContext.GarmentUnitReceiptNoteItems on a.Id equals b.URNId
                          join d in dbContext.GarmentDeliveryOrderDetails on b.POId equals d.POId
                          join f in dbContext.GarmentInternalPurchaseOrders on b.POId equals f.Id
                          //join f in SaldoAwal on b.POId equals f.POID
                          join e in dbContext.GarmentReceiptCorrectionItems on b.Id equals e.URNItemId into RC
                          from ty in RC.DefaultIfEmpty()
                          join c in dbContext.GarmentUnitExpenditureNoteItems on b.UENItemId equals c.Id into UE
                          from ww in UE.DefaultIfEmpty()
                          join r in dbContext.GarmentUnitExpenditureNotes on ww.UENId equals r.Id into UEN
                          from dd in UEN.DefaultIfEmpty()
                          where d.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? d.CodeRequirment : ctg) &&
                          a.IsDeleted == false && b.IsDeleted == false
                          //String.IsNullOrEmpty(a.UENNo) ? false : a.UENNo.Contains(unitcode)
                          //|| a.UnitCode == unitcode
                          //a.UnitCode == unitcode || a.UENNo.Contains(unitcode)
                          // a.UENNo.Contains(unitcode) || a.UnitCode == unitcode     /*String.IsNullOrEmpty(a.UENNo) ? true :*/
                          && a.CreatedUtc.AddHours(offset).Date >= DateFrom.Date &&
                          a.CreatedUtc.AddHours(offset).Date <= DateTo.Date

                          select new
            {
                ReceiptDate     = a.ReceiptDate,
                CodeRequirment  = d.CodeRequirment,
                ProductCode     = b.ProductCode,
                ProductName     = b.ProductName,
                RO              = b.RONo,
                Uom             = b.UomUnit,
                Buyer           = f.BuyerCode,
                PlanPo          = b.POSerialNumber,
                NoArticle       = f.Article,
                QtyReceipt      = b.ReceiptQuantity,
                QtyCorrection   = ty.POSerialNumber == null ? 0 : ty.Quantity,
                QtyExpend       = ww.POSerialNumber == null ? 0 : ww.Quantity,
                PriceReceipt    = b.PricePerDealUnit,
                PriceCorrection = ty.POSerialNumber == null ? 0 : ty.PricePerDealUnit,
                PriceExpend     = ww.POSerialNumber == null ? 0 : ww.PricePerDealUnit,
                POId            = b.POId,
                URNType         = a.URNType,
                UnitCode        = a.UnitCode,
                UENNo           = a.UENNo,
                UnitSenderCode  = dd.UnitSenderCode == null ? "-" : dd.UnitSenderCode,
                UnitRequestName = dd.UnitRequestName == null ? "-" : dd.UnitRequestName,
                ExpenditureTo   = dd.ExpenditureTo == null ? "-" : dd.ExpenditureTo,
                a.IsDeleted
            };
            var CobaPPAkhir = from a in PPAkhir
                              where a.UENNo.Contains((String.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)) || a.UnitCode == (String.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)
                              //where a.ReceiptDate.AddHours(offset).Date >= DateFrom.Date
                              //      && a.ReceiptDate.AddHours(offset).Date <= DateTo.Date
                              //      && a.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? a.CodeRequirment : ctg)
                              //      && a.IsDeleted == false
                              select a;

            var SaldoAwal = from query in CobaPP
                            group query by new { query.ProductCode, query.ProductName, query.RO, query.PlanPo, query.POId, query.UnitCode, query.UnitSenderCode, query.UnitRequestName } into data
                select new AccountingStockReportViewModel
            {
                ProductCode            = data.Key.ProductCode,
                ProductName            = data.FirstOrDefault().ProductName,
                RO                     = data.Key.RO,
                Buyer                  = data.FirstOrDefault().Buyer,
                PlanPo                 = data.FirstOrDefault().PlanPo,
                NoArticle              = data.FirstOrDefault().NoArticle,
                BeginningBalanceQty    = data.Sum(x => x.QtyReceipt) + Convert.ToDecimal(data.Sum(x => x.QtyCorrection)) - Convert.ToDecimal(data.Sum(x => x.QtyExpend)),
                BeginningBalanceUom    = data.FirstOrDefault().Uom,
                BeginningBalancePrice  = data.Sum(x => x.PriceReceipt) + Convert.ToDecimal(data.Sum(x => x.PriceCorrection)) - Convert.ToDecimal(data.Sum(x => x.PriceExpend)),
                ReceiptCorrectionQty   = 0,
                ReceiptPurchaseQty     = 0,
                ReceiptProcessQty      = 0,
                ReceiptKon2AQty        = 0,
                ReceiptKon2BQty        = 0,
                ReceiptKon2CQty        = 0,
                ReceiptKon1MNSQty      = 0,
                ReceiptKon2DQty        = 0,
                ReceiptCorrectionPrice = 0,
                ReceiptPurchasePrice   = 0,
                ReceiptProcessPrice    = 0,
                ReceiptKon2APrice      = 0,
                ReceiptKon2BPrice      = 0,
                ReceiptKon2CPrice      = 0,
                ReceiptKon1MNSPrice    = 0,
                ReceiptKon2DPrice      = 0,
                ExpendReturQty         = 0,
                ExpendRestQty          = 0,
                ExpendProcessQty       = 0,
                ExpendSampleQty        = 0,
                ExpendKon2AQty         = 0,
                ExpendKon2BQty         = 0,
                ExpendKon2CQty         = 0,
                ExpendKon1MNSQty       = 0,
                ExpendKon2DQty         = 0,
                ExpendReturPrice       = 0,
                ExpendRestPrice        = 0,
                ExpendProcessPrice     = 0,
                ExpendSamplePrice      = 0,
                ExpendKon2APrice       = 0,
                ExpendKon2BPrice       = 0,
                ExpendKon2CPrice       = 0,
                ExpendKon1MNSPrice     = 0,
                ExpendKon2DPrice       = 0,
                EndingBalanceQty       = 0,
                EndingBalancePrice     = 0,
                POId                   = data.FirstOrDefault().POId
            };
            var SaldoAkhir = from query in CobaPPAkhir
                             group query by new { query.ProductCode, query.ProductName, query.RO, query.PlanPo, query.POId, query.UnitCode, query.UnitSenderCode, query.UnitRequestName } into data
                select new AccountingStockReportViewModel
            {
                ProductCode           = data.Key.ProductCode,
                ProductName           = data.Key.ProductName,
                RO                    = data.Key.RO,
                Buyer                 = data.FirstOrDefault().Buyer,
                PlanPo                = data.FirstOrDefault().PlanPo,
                NoArticle             = data.FirstOrDefault().NoArticle,
                BeginningBalanceQty   = /*data.Sum(x => x.QtyReceipt) + Convert.ToDecimal(data.Sum(x => x.QtyCorrection)) - Convert.ToDecimal(data.Sum(x => x.QtyExpend))*/ 0,
                BeginningBalanceUom   = data.FirstOrDefault().Uom,
                BeginningBalancePrice = /*data.Sum(x => x.PriceReceipt) + Convert.ToDecimal(data.Sum(x => x.PriceCorrection)) - Convert.ToDecimal(data.Sum(x => x.PriceExpend))*/ 0,
                ReceiptCorrectionQty  = 0,
                ReceiptPurchaseQty    = data.FirstOrDefault().URNType == "PEMBELIAN" && data.FirstOrDefault().UnitCode == unitcode?data.Sum(x => x.QtyReceipt) : 0,
                                            ReceiptProcessQty = data.FirstOrDefault().URNType == "PROSES" && data.FirstOrDefault().UnitCode == unitcode?data.Sum(x => x.QtyReceipt) : 0,
                                                                    ReceiptKon2AQty        = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C2A" ? data.Sum(x => x.QtyReceipt) : 0,
                                                                    ReceiptKon2BQty        = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C2B" ? data.Sum(x => x.QtyReceipt) : 0,
                                                                    ReceiptKon2CQty        = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C2C" ? data.Sum(x => x.QtyReceipt) : 0,
                                                                    ReceiptKon1MNSQty      = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C1A" ? data.Sum(x => x.QtyReceipt) : 0,
                                                                    ReceiptKon2DQty        = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C1B" ? data.Sum(x => x.QtyReceipt) : 0,
                                                                    ReceiptCorrectionPrice = 0,
                                                                    ReceiptPurchasePrice   = data.FirstOrDefault().URNType == "PEMBELIAN" && data.FirstOrDefault().UnitCode == unitcode?data.Sum(x => x.PriceReceipt) : 0,
                                                                                                 ReceiptProcessPrice = data.FirstOrDefault().URNType == "PROSES" && data.FirstOrDefault().UnitCode == unitcode?data.Sum(x => x.PriceReceipt) : 0,
                                                                                                                           ReceiptKon2APrice   = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C2A" ? data.Sum(x => x.PriceReceipt) : 0,
                                                                                                                           ReceiptKon2BPrice   = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C2B" ? data.Sum(x => x.PriceReceipt) : 0,
                                                                                                                           ReceiptKon2CPrice   = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C2C" ? data.Sum(x => x.PriceReceipt) : 0,
                                                                                                                           ReceiptKon1MNSPrice = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C1A" ? data.Sum(x => x.PriceReceipt) : 0,
                                                                                                                           ReceiptKon2DPrice   = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C1B" ? data.Sum(x => x.PriceReceipt) : 0,
                                                                                                                           ExpendReturQty      = data.FirstOrDefault().ExpenditureTo == "EXTERNAL" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.QtyExpend) : 0,
                                                                                                                                                     ExpendRestQty    = 0,
                                                                                                                                                     ExpendProcessQty = data.FirstOrDefault().ExpenditureTo == "PROSES" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.QtyExpend) : 0,
                                                                                                                                                                            ExpendSampleQty = data.FirstOrDefault().ExpenditureTo == "SAMPLE" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.QtyExpend) : 0,
                                                                                                                                                                                                  ExpendKon2AQty = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 2A" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.QtyExpend) : 0,
                                                                                                                                                                                                                       ExpendKon2BQty = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 2B" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.QtyExpend) : 0,
                                                                                                                                                                                                                                            ExpendKon2CQty = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 2C" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.QtyExpend) : 0,
                                                                                                                                                                                                                                                                 ExpendKon1MNSQty = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 1A" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.QtyExpend) : 0,
                                                                                                                                                                                                                                                                                        ExpendKon2DQty = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 1B" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.QtyExpend) : 0,
                                                                                                                                                                                                                                                                                                             ExpendReturPrice = data.FirstOrDefault().ExpenditureTo == "EXTERNAL" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.PriceExpend) : 0,
                                                                                                                                                                                                                                                                                                                                    ExpendRestPrice    = 0,
                                                                                                                                                                                                                                                                                                                                    ExpendProcessPrice = data.FirstOrDefault().ExpenditureTo == "PROSES" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.PriceExpend) : 0,
                                                                                                                                                                                                                                                                                                                                                             ExpendSamplePrice = data.FirstOrDefault().ExpenditureTo == "SAMPLE" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.PriceExpend) : 0,
                                                                                                                                                                                                                                                                                                                                                                                     ExpendKon2APrice = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 2A" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.PriceExpend) : 0,
                                                                                                                                                                                                                                                                                                                                                                                                            ExpendKon2BPrice = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 2B" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.PriceExpend) : 0,
                                                                                                                                                                                                                                                                                                                                                                                                                                   ExpendKon2CPrice = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 2C" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.PriceExpend) : 0,
                                                                                                                                                                                                                                                                                                                                                                                                                                                          ExpendKon1MNSPrice = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 1A" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.PriceExpend) : 0,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   ExpendKon2DPrice = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 1B" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.PriceExpend) : 0,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          EndingBalanceQty   = Convert.ToDecimal(Convert.ToDouble(data.Sum(x => x.QtyReceipt)) - data.Sum(x => x.QtyExpend)),
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          EndingBalancePrice = Convert.ToDecimal(Convert.ToDouble(data.Sum(x => x.PriceReceipt)) - data.Sum(x => x.PriceExpend)),
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          POId = data.FirstOrDefault().POId
            };

            List <AccountingStockReportViewModel> Data1 = SaldoAwal.Concat(SaldoAkhir).ToList();

            var Data = (from query in Data1
                        group query by new { query.POId, query.ProductCode, query.RO } into groupdata
                        select new AccountingStockReportViewModel
            {
                ProductCode = groupdata.FirstOrDefault().ProductCode == null ? "-" : groupdata.FirstOrDefault().ProductCode,
                ProductName = groupdata.FirstOrDefault().ProductName == null ? "-" : groupdata.FirstOrDefault().ProductName,
                RO = groupdata.FirstOrDefault().RO == null ? "-" : groupdata.FirstOrDefault().RO,
                Buyer = groupdata.FirstOrDefault().Buyer == null ? "-" : groupdata.FirstOrDefault().Buyer,
                PlanPo = groupdata.FirstOrDefault().PlanPo == null ? "-" : groupdata.FirstOrDefault().PlanPo,
                NoArticle = groupdata.FirstOrDefault().NoArticle == null ? "-" : groupdata.FirstOrDefault().NoArticle,
                BeginningBalanceQty = groupdata.Sum(x => x.BeginningBalanceQty),
                BeginningBalanceUom = groupdata.FirstOrDefault().BeginningBalanceUom,
                BeginningBalancePrice = groupdata.Sum(x => x.BeginningBalancePrice),
                ReceiptCorrectionQty = 0,
                ReceiptPurchaseQty = groupdata.Sum(x => x.ReceiptPurchaseQty),
                ReceiptProcessQty = groupdata.Sum(x => x.ReceiptProcessQty),
                ReceiptKon2AQty = groupdata.Sum(x => x.ReceiptKon2AQty),
                ReceiptKon2BQty = groupdata.Sum(x => x.ReceiptKon2BQty),
                ReceiptKon2CQty = groupdata.Sum(x => x.ReceiptKon2CQty),
                ReceiptKon1MNSQty = groupdata.Sum(x => x.ReceiptKon1MNSQty),
                ReceiptKon2DQty = groupdata.Sum(x => x.ReceiptKon2DQty),
                ReceiptCorrectionPrice = 0,
                ReceiptPurchasePrice = groupdata.Sum(x => x.ReceiptPurchasePrice),
                ReceiptProcessPrice = groupdata.Sum(x => x.ReceiptProcessPrice),
                ReceiptKon2APrice = groupdata.Sum(x => x.ReceiptKon2APrice),
                ReceiptKon2BPrice = groupdata.Sum(x => x.ReceiptKon2BPrice),
                ReceiptKon2CPrice = groupdata.Sum(x => x.ReceiptKon2CPrice),
                ReceiptKon1MNSPrice = groupdata.Sum(x => x.ReceiptKon1MNSPrice),
                ReceiptKon2DPrice = groupdata.Sum(x => x.ReceiptKon2DPrice),
                ExpendReturQty = groupdata.Sum(x => x.ExpendReturQty),
                ExpendRestQty = 0,
                ExpendProcessQty = groupdata.Sum(x => x.ExpendProcessQty),
                ExpendSampleQty = groupdata.Sum(x => x.ExpendSampleQty),
                ExpendKon2AQty = groupdata.Sum(x => x.ExpendKon2AQty),
                ExpendKon2BQty = groupdata.Sum(x => x.ExpendKon2BQty),
                ExpendKon2CQty = groupdata.Sum(x => x.ExpendKon2CQty),
                ExpendKon1MNSQty = groupdata.Sum(x => x.ExpendKon1MNSQty),
                ExpendKon2DQty = groupdata.Sum(x => x.ExpendKon2DQty),
                ExpendReturPrice = groupdata.Sum(x => x.ExpendReturPrice),
                ExpendRestPrice = 0,
                ExpendProcessPrice = groupdata.Sum(x => x.ExpendProcessPrice),
                ExpendSamplePrice = groupdata.Sum(x => x.ExpendSamplePrice),
                ExpendKon2APrice = groupdata.Sum(x => x.ExpendKon2APrice),
                ExpendKon2BPrice = groupdata.Sum(x => x.ExpendKon2BPrice),
                ExpendKon2CPrice = groupdata.Sum(x => x.ExpendKon2CPrice),
                ExpendKon1MNSPrice = groupdata.Sum(x => x.ExpendKon1MNSPrice),
                ExpendKon2DPrice = groupdata.Sum(x => x.ExpendKon2DPrice),
                EndingBalanceQty = Convert.ToDecimal((groupdata.Sum(x => x.BeginningBalanceQty) + groupdata.Sum(x => x.ReceiptPurchaseQty) + groupdata.Sum(x => x.ReceiptProcessQty) + groupdata.Sum(x => x.ReceiptKon2AQty) + groupdata.Sum(x => x.ReceiptKon2BQty) + groupdata.Sum(x => x.ReceiptKon2CQty) + groupdata.Sum(x => x.ReceiptKon1MNSQty) + groupdata.Sum(x => x.ReceiptKon2DQty) + 0) - (Convert.ToDecimal(groupdata.Sum(x => x.ExpendProcessQty)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendSampleQty)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon2AQty)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon2BQty)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon2CQty)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon1MNSQty)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon2DQty)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendReturPrice)))),
                EndingBalancePrice = Convert.ToDecimal((groupdata.Sum(x => x.BeginningBalancePrice) + groupdata.Sum(x => x.ReceiptPurchasePrice) + groupdata.Sum(x => x.ReceiptProcessPrice) + groupdata.Sum(x => x.ReceiptKon2APrice) + groupdata.Sum(x => x.ReceiptKon2BPrice) + groupdata.Sum(x => x.ReceiptKon2CPrice) + groupdata.Sum(x => x.ReceiptKon1MNSPrice) + groupdata.Sum(x => x.ReceiptKon2DPrice) + 0) - (Convert.ToDecimal(groupdata.Sum(x => x.ExpendReturPrice)) + 0 + Convert.ToDecimal(groupdata.Sum(x => x.ExpendProcessPrice)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendSamplePrice)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon2APrice)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon2BPrice)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon2CPrice)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon1MNSPrice)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon2DPrice))))
            });


            return(Data.AsQueryable());
        }
Beispiel #3
0
        public List <GarmentStockReportViewModel> GetStockQuery(string ctg, string unitcode, DateTime?datefrom, DateTime?dateto, int offset)
        {
            DateTime DateFrom = datefrom == null ? new DateTime(1970, 1, 1) : (DateTime)datefrom;
            DateTime DateTo   = dateto == null ? DateTime.Now : (DateTime)dateto;

            var lastdate = dbContext.BalanceStocks.OrderByDescending(x => x.CreateDate).Select(x => x.CreateDate).FirstOrDefault();

            var BalaceStock = (from a in dbContext.BalanceStocks
                               where a.CreateDate == lastdate
                               group a by new { a.ArticleNo, a.EPOID, a.EPOItemId } into data
                               select new
            {
                BalanceStockId = data.FirstOrDefault().BalanceStockId,
                ArticleNo = data.FirstOrDefault().ArticleNo,
                EPOID = data.FirstOrDefault().EPOID,
                EPOItemId = data.FirstOrDefault().EPOItemId,
                CloseStock = (double)data.Sum(x => x.CloseStock),
                ClosePrice = (decimal)data.Sum(x => x.ClosePrice)
            }).ToList();


            //var saldoawalbalanceepoid = SaldoAwal.Select(x => x.EPOID).ToList();

            var SAkhir = (from a in dbContext.GarmentUnitReceiptNotes
                          join b in dbContext.GarmentUnitReceiptNoteItems on a.Id equals b.URNId
                          join h in dbContext.GarmentDeliveryOrderDetails on b.DODetailId equals h.Id
                          join f in dbContext.GarmentInternalPurchaseOrders on b.POId equals f.Id
                          join e in dbContext.GarmentReceiptCorrectionItems on b.Id equals e.URNItemId into RC
                          from ty in RC.DefaultIfEmpty()
                          join c in dbContext.GarmentUnitExpenditureNoteItems on b.Id equals c.URNItemId into UE
                          from ww in UE.DefaultIfEmpty()
                          join r in dbContext.GarmentUnitExpenditureNotes on ww.UENId equals r.Id into UEN
                          from dd in UEN.DefaultIfEmpty()
                          join epoItem in dbContext.GarmentExternalPurchaseOrderItems on b.EPOItemId equals epoItem.Id into EP
                          from epoItem in EP.DefaultIfEmpty()
                          join epo in dbContext.GarmentExternalPurchaseOrders on epoItem.GarmentEPOId equals epo.Id into EPO
                          from epo in EPO.DefaultIfEmpty()
                          where h.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? h.CodeRequirment : ctg) &&
                          a.IsDeleted == false && b.IsDeleted == false &&
                          a.CreatedUtc.AddHours(offset).Date >= DateFrom.Date &&
                          a.CreatedUtc.AddHours(offset).Date <= DateTo.Date &&
                          a.UnitCode == (string.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)
                          select new
            {
                URNId = a.Id,
                URNItemsId = b.Id,
                DODEtailId = h.Id,
                IPOId = f.Id,
                RCorrId = ty == null ? 0 : ty.Id,
                UENItemsId = ww == null ? 0 : ww.Id,
                UENId = dd == null ? 0 : dd.Id,
                EPOItemId = epoItem == null ? 0 : epoItem.Id,
                EPOId = epo == null ? 0 : epo.Id,
                UnitCode = a.UnitCode,
                UnitSenderCode = dd == null ? "-" : dd.UnitSenderCode
            }).Distinct().ToList();
            //SaldoAkhir = SaldoAkhir.Where(x => x.UENNo.Contains((String.IsNullOrWhiteSpace(unitcode) ? x.UnitCode : unitcode)) || x.UnitCode == ((String.IsNullOrWhiteSpace(unitcode) ? x.UnitCode : unitcode))).Select(x => x).ToList();
            var SAkhirs                      = SAkhir.Select(x => x).ToList();
            var unitreceiptnoteIds           = SAkhirs.Select(x => x.URNId).ToList();
            var unitreceiptnotes             = dbContext.GarmentUnitReceiptNotes.Where(x => unitreceiptnoteIds.Contains(x.Id)).Select(s => new { s.ReceiptDate, s.URNType, s.UnitCode, s.Id }).ToList();
            var unitreceiptnoteItemIds       = SAkhirs.Select(x => x.URNItemsId).ToList();
            var unitreceiptnoteItems         = dbContext.GarmentUnitReceiptNoteItems.Where(x => unitreceiptnoteItemIds.Contains(x.Id)).Select(s => new { s.ProductCode, s.ProductName, s.ProductRemark, s.RONo, s.SmallUomUnit, s.POSerialNumber, s.ReceiptQuantity, s.PricePerDealUnit, s.Id }).ToList();
            var deliveryorderdetailid        = SAkhirs.Select(x => x.DODEtailId).ToList();
            var deliveryorderdetails         = dbContext.GarmentDeliveryOrderDetails.Where(x => deliveryorderdetailid.Contains(x.Id)).Select(s => new { s.CodeRequirment, s.Id }).ToList();
            var purchaseorderIds             = SAkhirs.Select(x => x.IPOId).ToList();
            var purchaseorders               = dbContext.GarmentInternalPurchaseOrders.Where(x => purchaseorderIds.Contains(x.Id)).Select(s => new { s.BuyerCode, s.Id, s.Article }).ToList();
            var receiptCorrectionitemIds     = SAkhirs.Select(x => x.RCorrId).ToList();
            var receiptCorrectionitems       = dbContext.GarmentReceiptCorrectionItems.Where(x => receiptCorrectionitemIds.Contains(x.Id)).Select(s => new { s.CorrectionQuantity, s.PricePerDealUnit, s.Id }).ToList();
            var unitexpenditureitemIds       = SAkhirs.Select(x => x.UENItemsId).ToList();
            var unitexpenditureitems         = dbContext.GarmentUnitExpenditureNoteItems.Where(x => unitexpenditureitemIds.Contains(x.Id)).Select(s => new { s.PricePerDealUnit, s.Quantity, s.Id }).ToList();
            var unitexpenditureIds           = SAkhirs.Select(x => x.UENId).ToList();
            var unitexpenditures             = dbContext.GarmentUnitExpenditureNotes.Where(x => unitexpenditureIds.Contains(x.Id)).Select(s => new { s.UnitRequestCode, s.UnitSenderCode, s.ExpenditureTo, s.Id }).ToList();
            var externalpurchaseorderitemIds = SAkhirs.Select(x => x.EPOItemId).ToList();
            var externalpurchaseorderitems   = dbContext.GarmentExternalPurchaseOrderItems.Where(x => externalpurchaseorderitemIds.Contains(x.Id)).Select(s => new { s.Id }).ToList();
            var externalpurchaseorderIds     = SAkhirs.Select(x => x.EPOId).ToList();
            var externalpurchaseorders       = dbContext.GarmentExternalPurchaseOrders.Where(x => externalpurchaseorderIds.Contains(x.Id)).Select(s => new { s.PaymentMethod, s.Id }).ToList();
            //var balancestocks = SaldoAwal.Where(x => externalpurchaseorderitemIds.Contains((long)x.EPOItemId)).Select(s => new { s.BeginningBalanceQty, s.BeginningBaancePrice, s.EPOItemId}).ToList();
            List <GarmentStockReportViewModel> SaldoAkhir = new List <GarmentStockReportViewModel>();

            foreach (var item in SAkhir)
            {
                var unitreceiptnote           = unitreceiptnotes.Where(x => x.Id.Equals(item.URNId)).FirstOrDefault();
                var unitreceiptnoteItem       = unitreceiptnoteItems.FirstOrDefault(x => x.Id.Equals(item.URNItemsId));
                var deliveryorderdetail       = deliveryorderdetails.FirstOrDefault(x => x.Id.Equals(item.DODEtailId));
                var purchaseorder             = purchaseorders.FirstOrDefault(x => x.Id.Equals(item.IPOId));
                var receiptCorrectionitem     = receiptCorrectionitems.FirstOrDefault(x => x.Id.Equals(item.RCorrId));
                var unitexpenditureitem       = unitexpenditureitems.FirstOrDefault(x => x.Id.Equals(item.UENItemsId));
                var unitexpenditure           = unitexpenditures.FirstOrDefault(x => x.Id.Equals(item.UENId));
                var externalpurchaseorderitem = externalpurchaseorderitems.FirstOrDefault(x => x.Id.Equals(item.EPOItemId));
                var externalpurchaseorder     = externalpurchaseorders.FirstOrDefault(x => x.Id.Equals(item.EPOId));
                //var balancestock = balancestocks.FirstOrDefault(x => x.EPOItemId.Equals(item.EPOItemId));

                SaldoAkhir.Add(new GarmentStockReportViewModel
                {
                    BeginningBalanceQty = 0,
                    BeginningBalanceUom = unitreceiptnoteItem.SmallUomUnit,
                    Buyer                = purchaseorder.BuyerCode,
                    EndingBalanceQty     = 0,
                    EndingUom            = unitreceiptnoteItem.SmallUomUnit,
                    ExpandUom            = unitreceiptnoteItem.SmallUomUnit,
                    ExpendQty            = unitexpenditureitem == null ? 0 : unitexpenditureitem.Quantity,
                    NoArticle            = purchaseorder.Article,
                    PaymentMethod        = externalpurchaseorder == null ? "-" : externalpurchaseorder.PaymentMethod,
                    PlanPo               = unitreceiptnoteItem == null ? "-" : unitreceiptnoteItem.POSerialNumber,
                    POId                 = purchaseorder.Id,
                    ProductCode          = unitreceiptnoteItem.ProductCode,
                    ProductName          = unitreceiptnoteItem.ProductName,
                    ProductRemark        = unitreceiptnoteItem.ProductRemark,
                    ReceiptCorrectionQty = receiptCorrectionitem == null ? 0 : (decimal)receiptCorrectionitem.CorrectionQuantity,
                    ReceiptQty           = unitreceiptnoteItem.ReceiptQuantity,
                    ReceiptUom           = unitreceiptnoteItem.SmallUomUnit,
                    RO              = unitreceiptnoteItem.RONo,
                    UNitCode        = unitreceiptnote.UnitCode,
                    UnitSenderCode  = unitexpenditure == null ? "-" : unitexpenditure.UnitSenderCode,
                    UnitRequestCode = unitexpenditure == null ? "-" : unitexpenditure.UnitRequestCode
                });
            }

            SaldoAkhir = (from query in SaldoAkhir
                          group query by new { query.ProductCode, query.ProductName, query.RO, query.PlanPo, query.POId, query.UNitCode, query.UnitSenderCode } into data
                          //group query by new { query.POId } into data
                          select new GarmentStockReportViewModel
            {
                BeginningBalanceQty = data.Sum(x => x.BeginningBalanceQty),
                BeginningBalanceUom = data.FirstOrDefault().BeginningBalanceUom,
                Buyer = data.FirstOrDefault().Buyer,
                EndingBalanceQty = data.FirstOrDefault().EndingBalanceQty,
                EndingUom = data.FirstOrDefault().EndingUom,
                ExpandUom = data.FirstOrDefault().EndingUom,
                ExpendQty = data.Sum(x => x.ExpendQty),
                NoArticle = data.FirstOrDefault().NoArticle,
                PaymentMethod = data.FirstOrDefault().PaymentMethod,
                PlanPo = data.FirstOrDefault().PlanPo,
                POId = data.FirstOrDefault().POId,
                ProductCode = data.FirstOrDefault().ProductCode,
                ProductName = data.FirstOrDefault().ProductName,
                ProductRemark = data.FirstOrDefault().ProductRemark,
                ReceiptCorrectionQty = data.Sum(x => x.ReceiptCorrectionQty),
                //ReceiptQty = data.Sum(x => x.ReceiptQty),
                ReceiptQty = data.FirstOrDefault().ReceiptQty,
                ReceiptUom = data.FirstOrDefault().ReceiptUom,
                RO = data.FirstOrDefault().RO,
                UNitCode = data.FirstOrDefault().UNitCode,
                UnitSenderCode = data.FirstOrDefault().UnitSenderCode,
                UnitRequestCode = data.FirstOrDefault().UnitRequestCode
            }).ToList();
            var SaldoAkhirId = SaldoAkhir.Select(x => x.POId).ToList();
            var SA           = (from a in dbContext.GarmentUnitReceiptNotes
                                join b in dbContext.GarmentUnitReceiptNoteItems on a.Id equals b.URNId
                                join i in dbContext.GarmentInternalPurchaseOrders on b.POId equals i.Id
                                join c in dbContext.GarmentDeliveryOrderDetails on b.DODetailId equals c.Id
                                join e in dbContext.GarmentUnitExpenditureNoteItems on b.Id equals e.URNItemId into UENItem
                                from ee in UENItem.DefaultIfEmpty()
                                join h in dbContext.GarmentReceiptCorrectionItems on b.Id equals h.URNItemId into RC
                                from hh in RC.DefaultIfEmpty()
                                join f in dbContext.GarmentUnitExpenditureNotes on ee.UENId equals f.Id into UEN
                                from ff in UEN.DefaultIfEmpty()
                                join g in dbContext.GarmentExternalPurchaseOrders on b.EPOItemId equals g.Id into Exter
                                from gg in Exter.DefaultIfEmpty()
                                join epoItem in dbContext.GarmentExternalPurchaseOrderItems on b.EPOItemId equals epoItem.Id into EP
                                from epoItem in EP.DefaultIfEmpty()
                                join epo in dbContext.GarmentExternalPurchaseOrders on epoItem.GarmentEPOId equals epo.Id into EPO
                                from epo in EPO.DefaultIfEmpty()
                                where c.CodeRequirment == (string.IsNullOrWhiteSpace(ctg) ? c.CodeRequirment : ctg) &&
                                a.IsDeleted == false && b.IsDeleted == false &&
                                a.CreatedUtc > lastdate && a.CreatedUtc < DateFrom &&
                                a.UnitCode == (string.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode) &&
                                SaldoAkhirId.Contains(i.Id)
                                select new
            {
                //BSId = bb == null ? "" : bb.BalanceStockId,
                URNItemsId = b == null ? 0 : b.Id,
                UENItemId = ee == null ? 0 : ee.Id,
                RCorItemId = hh != null ? hh.Id : 0,
                URNId = a == null ? 0 : a.Id,
                DODEtailId = c == null ? 0 : c.Id,
                IPOId = i == null ? 0 : i.Id,
                RCorrId = hh == null ? 0 : hh.Id,
                //UENItemsId = ee == null ? 0 : ee.Id,
                UENId = ff == null ? 0 : ff.Id,
                UnitCode = a == null ? "-" : a.UnitCode,
                UnitSenderCode = ff == null ? "-" : ff.UnitSenderCode,
                EPOItemId = epoItem == null ? 0 : epoItem.Id,
                EPOId = epo == null ? 0 : epo.Id,
                //SAQty = (bb != null ? bb.CloseStock : 0) + (hh != null ? hh.CorrectionQuantity : 0) + (double)b.ReceiptQuantity - (ee != null ? ee.Quantity : 0),
                //SAPrice = (bb != null ? bb.ClosePrice : 0) + (hh != null ? (decimal)hh.PricePerDealUnit * (decimal)hh.CorrectionQuantity : 0) +  b.PricePerDealUnit * b.ReceiptQuantity - (ee != null ? (decimal)ee.PricePerDealUnit * (decimal)ee.Quantity : 0)
            }).ToList();

            var SaldoAwals = SA.Select(x => x).ToList().Distinct();
            var saldoawalunitreceiptnoteIds       = SaldoAwals.Select(x => x.URNId).ToList();
            var saldoawalunitreceiptnotes         = dbContext.GarmentUnitReceiptNotes.Where(x => saldoawalunitreceiptnoteIds.Contains(x.Id)).Select(s => new { s.ReceiptDate, s.URNType, s.UnitCode, s.Id }).ToList();
            var saldoawalunitreceiptnoteItemIds   = SaldoAwals.Select(x => x.URNItemsId).ToList();
            var saldoawalunitreceiptnoteItems     = dbContext.GarmentUnitReceiptNoteItems.Where(x => saldoawalunitreceiptnoteItemIds.Contains(x.Id)).Select(s => new { s.ProductCode, s.ProductName, s.ProductRemark, s.RONo, s.SmallUomUnit, s.POSerialNumber, s.ReceiptQuantity, s.PricePerDealUnit, s.Id }).ToList();
            var saldoawaldeliveryorderdetailid    = SaldoAwals.Select(x => x.DODEtailId).ToList();
            var saldoawaldeliveryorderdetails     = dbContext.GarmentDeliveryOrderDetails.Where(x => saldoawaldeliveryorderdetailid.Contains(x.Id)).Select(s => new { s.CodeRequirment, s.Id }).ToList();
            var saldoawalpurchaseorderIds         = SaldoAwals.Select(x => x.IPOId).ToList();
            var saldoawalpurchaseorders           = dbContext.GarmentInternalPurchaseOrders.Where(x => saldoawalpurchaseorderIds.Contains(x.Id)).Select(s => new { s.BuyerCode, s.Id, s.Article }).ToList();
            var saldoawalreceiptCorrectionitemIds = SaldoAwals.Select(x => x.RCorrId).ToList();
            var saldoawalreceiptCorrectionitems   = dbContext.GarmentReceiptCorrectionItems.Where(x => saldoawalreceiptCorrectionitemIds.Contains(x.Id)).Select(s => new { s.CorrectionQuantity, s.PricePerDealUnit, s.Id }).ToList();
            var saldoawalunitexpenditureitemIds   = SaldoAwals.Select(x => x.UENItemId).ToList();
            var saldoawalunitexpenditureitems     = dbContext.GarmentUnitExpenditureNoteItems.Where(x => saldoawalunitexpenditureitemIds.Contains(x.Id)).Select(s => new { s.PricePerDealUnit, s.Quantity, s.Id }).ToList();
            var saldoawalunitexpenditureIds       = SaldoAwals.Select(x => x.UENId).ToList();
            var saldoawalunitexpenditures         = dbContext.GarmentUnitExpenditureNotes.Where(x => saldoawalunitexpenditureIds.Contains(x.Id)).Select(s => new { s.UnitRequestCode, s.UnitSenderCode, s.ExpenditureTo, s.Id }).ToList();
            //var saldoawalbalancestockepoitemids = SaldoAwals.Select(x => x.BSId).ToList();

            var saldoawalexternalpurchaseorderitemIds = SaldoAwals.Select(x => x.EPOItemId).ToList();
            var saldoawalexternalpurchaseorderitems   = dbContext.GarmentExternalPurchaseOrderItems.Where(x => saldoawalexternalpurchaseorderitemIds.Contains(x.Id)).Select(s => new { s.Id }).ToList();
            var saldoawalexternalpurchaseorderIds     = SaldoAwals.Select(x => x.EPOId).ToList();
            var saldoawalexternalpurchaseorders       = dbContext.GarmentExternalPurchaseOrders.Where(x => saldoawalexternalpurchaseorderIds.Contains(x.Id)).Select(s => new { s.PaymentMethod, s.Id }).ToList();
            var saldoawalbalancestocks = BalaceStock.Where(x => saldoawalexternalpurchaseorderitemIds.Contains((long)x.EPOItemId)).Select(s => new { s.ArticleNo, s.ClosePrice, s.CloseStock, s.EPOID, s.EPOItemId, s.BalanceStockId }).ToList();
            List <GarmentStockReportViewModel> SaldoAwal = new List <GarmentStockReportViewModel>();

            foreach (var i in SaldoAwals)
            {
                var saldoawalunitreceiptnote           = saldoawalunitreceiptnotes.AsParallel().FirstOrDefault(x => x.Id == i.URNId);
                var saldoawalunitreceiptnoteItem       = saldoawalunitreceiptnoteItems.AsParallel().FirstOrDefault(x => x.Id.Equals(i.URNItemsId));
                var saldoawaldeliveryorderdetail       = saldoawaldeliveryorderdetails.AsParallel().FirstOrDefault(x => x.Id.Equals(i.DODEtailId));
                var saldoawalpurchaseorder             = saldoawalpurchaseorders.AsParallel().FirstOrDefault(x => x.Id.Equals(i.IPOId));
                var saldoawalreceiptCorrectionitem     = saldoawalreceiptCorrectionitems.AsParallel().FirstOrDefault(x => x.Id.Equals(i.RCorrId));
                var saldoawalunitexpenditureitem       = saldoawalunitexpenditureitems.AsParallel().FirstOrDefault(x => x.Id.Equals(i.URNItemsId));
                var saldoawalunitexpenditure           = saldoawalunitexpenditures.AsParallel().FirstOrDefault(x => x.Id.Equals(i.URNId));
                var saldoawalbalancestock              = saldoawalbalancestocks.AsParallel().FirstOrDefault(x => x.EPOItemId.Equals(i.EPOItemId));
                var saldoawalexternalpurchaseorderitem = saldoawalexternalpurchaseorderitems.AsParallel().FirstOrDefault(x => x.Id.Equals(i.EPOItemId));
                var saldoawalexternalpurchaseorder     = saldoawalexternalpurchaseorders.AsParallel().FirstOrDefault(x => x.Id.Equals(i.EPOId));

                SaldoAwal.Add(new GarmentStockReportViewModel
                {
                    BeginningBalanceQty = saldoawalbalancestock == null ? (saldoawalreceiptCorrectionitem != null ? (decimal)saldoawalreceiptCorrectionitem.CorrectionQuantity : 0) + saldoawalunitreceiptnoteItem.ReceiptQuantity - (saldoawalunitexpenditureitem != null ? (decimal)saldoawalunitexpenditureitem.Quantity : 0) : (decimal)saldoawalbalancestock.CloseStock + (saldoawalreceiptCorrectionitem != null ? (decimal)saldoawalreceiptCorrectionitem.CorrectionQuantity : 0) + saldoawalunitreceiptnoteItem.ReceiptQuantity - (saldoawalunitexpenditureitem != null ? (decimal)saldoawalunitexpenditureitem.Quantity : 0),
                    //BeginningBalanceQty = saldoawalbalancestock != null ? (decimal)saldoawalbalancestock.CloseStock : 0) + (saldoawalreceiptCorrectionitem != null ? (decimal)saldoawalreceiptCorrectionitem.CorrectionQuantity : 0) + saldoawalunitreceiptnoteItem.ReceiptQuantity - (saldoawalunitexpenditureitem != null ? (decimal)saldoawalunitexpenditureitem.Quantity : 0,
                    BeginningBalanceUom = saldoawalunitreceiptnoteItem.SmallUomUnit,
                    Buyer                = saldoawalpurchaseorder.BuyerCode,
                    EndingBalanceQty     = 0,
                    EndingUom            = saldoawalunitreceiptnoteItem.SmallUomUnit,
                    ExpandUom            = saldoawalunitreceiptnoteItem.SmallUomUnit,
                    ExpendQty            = saldoawalunitexpenditureitem == null ? 0 : saldoawalunitexpenditureitem.Quantity,
                    NoArticle            = saldoawalpurchaseorder.Article,
                    PaymentMethod        = saldoawalexternalpurchaseorder == null ? "-" : saldoawalexternalpurchaseorder.PaymentMethod,
                    PlanPo               = saldoawalunitreceiptnoteItem == null ? "-" : saldoawalunitreceiptnoteItem.POSerialNumber,
                    POId                 = saldoawalpurchaseorder == null ? 0 : saldoawalpurchaseorder.Id,
                    ProductCode          = saldoawalunitreceiptnoteItem.ProductCode,
                    ProductName          = saldoawalunitreceiptnoteItem.ProductName,
                    ProductRemark        = saldoawalunitreceiptnoteItem.ProductRemark,
                    ReceiptCorrectionQty = saldoawalreceiptCorrectionitem == null ? 0 : (decimal)saldoawalreceiptCorrectionitem.CorrectionQuantity,
                    ReceiptQty           = saldoawalunitreceiptnoteItem.ReceiptQuantity,
                    ReceiptUom           = saldoawalunitreceiptnoteItem.SmallUomUnit,
                    RO              = saldoawalunitreceiptnoteItem.RONo,
                    UNitCode        = saldoawalunitreceiptnote.UnitCode,
                    UnitSenderCode  = saldoawalunitexpenditure == null ? "-" : saldoawalunitexpenditure.UnitSenderCode,
                    UnitRequestCode = saldoawalunitexpenditure == null ? "-" : saldoawalunitexpenditure.UnitRequestCode
                });
            }

            SaldoAwal = (from query in SaldoAwal
                         group query by new { query.ProductCode, query.ProductName, query.RO, query.PlanPo, query.POId, query.UNitCode, query.UnitSenderCode } into data
                         select new GarmentStockReportViewModel
            {
                BeginningBalanceQty = data.Sum(x => x.BeginningBalanceQty),
                BeginningBalanceUom = data.FirstOrDefault().BeginningBalanceUom,
                Buyer = data.FirstOrDefault().Buyer,
                EndingBalanceQty = data.FirstOrDefault().EndingBalanceQty,
                EndingUom = data.FirstOrDefault().EndingUom,
                ExpandUom = data.FirstOrDefault().EndingUom,
                ExpendQty = 0,
                NoArticle = data.FirstOrDefault().NoArticle,
                PaymentMethod = data.FirstOrDefault().PaymentMethod,
                PlanPo = data.FirstOrDefault().PlanPo,
                POId = data.FirstOrDefault().POId,
                ProductCode = data.FirstOrDefault().ProductCode,
                ProductName = data.FirstOrDefault().ProductName,
                ProductRemark = data.FirstOrDefault().ProductRemark,
                ReceiptCorrectionQty = 0,
                ReceiptQty = 0,
                ReceiptUom = data.FirstOrDefault().ReceiptUom,
                RO = data.FirstOrDefault().RO,
                UNitCode = data.FirstOrDefault().UNitCode,
                UnitSenderCode = data.FirstOrDefault().UnitSenderCode,
                UnitRequestCode = data.FirstOrDefault().UnitRequestCode
            }).ToList();


            var stock = (from a in SaldoAkhir
                         join b in SaldoAwal on a.POId equals b.POId into stockdata
                         from bb in stockdata.DefaultIfEmpty()
                         select new GarmentStockReportViewModel
            {
                BeginningBalanceQty = (bb == null ? 0 : bb.BeginningBalanceQty),
                BeginningBalanceUom = a.BeginningBalanceUom,
                Buyer = a.Buyer,
                EndingBalanceQty = (bb == null ? 0 : bb.BeginningBalanceQty) + a.ReceiptQty + a.ReceiptCorrectionQty - (decimal)a.ExpendQty,
                EndingUom = a.EndingUom,
                ExpendQty = a.ExpendQty,
                NoArticle = a.NoArticle,
                PaymentMethod = a.PaymentMethod,
                PlanPo = a.PlanPo,
                POId = a.POId,
                ProductCode = a.ProductCode,
                ProductName = a.ProductName,
                ProductRemark = a.ProductRemark,
                ReceiptCorrectionQty = a.ReceiptCorrectionQty,
                ReceiptQty = a.ReceiptQty,
                ReceiptUom = a.ReceiptUom,
                RO = a.RO,
                UNitCode = a.UNitCode,
                UnitSenderCode = a.UnitSenderCode,
                UnitRequestCode = a.UnitRequestCode
            }).ToList();

            //var stock = SaldoAwal.Concat(SaldoAkhir).ToList();
            //stock = (from query in stock
            //         group query by new { query.POId, query.ProductCode, query.RO } into data
            //         select new GarmentStockReportViewModel
            //         {
            //             BeginningBalanceQty = data.Sum(x => x.BeginningBalanceQty),
            //             BeginningBalanceUom = data.FirstOrDefault().BeginningBalanceUom,
            //             Buyer = data.FirstOrDefault().Buyer,
            //             EndingBalanceQty = data.Sum(x => x.BeginningBalanceQty) + data.Sum(x => x.ReceiptQty) + data.Sum(x => x.ReceiptCorrectionQty) - data.Sum(x => (decimal)x.ExpendQty),
            //             EndingUom = data.FirstOrDefault().EndingUom,
            //             ExpandUom = data.FirstOrDefault().EndingUom,
            //             ExpendQty = data.Sum(x => x.ExpendQty),
            //             NoArticle = data.FirstOrDefault().NoArticle,
            //             PaymentMethod = data.FirstOrDefault().PaymentMethod,
            //             PlanPo = data.FirstOrDefault().PlanPo,
            //             POId = data.FirstOrDefault().POId,
            //             ProductCode = data.FirstOrDefault().ProductCode,
            //             ProductName = data.FirstOrDefault().ProductName,
            //             ProductRemark = data.FirstOrDefault().ProductRemark,
            //             ReceiptCorrectionQty = data.Sum(x => x.ReceiptCorrectionQty),
            //             ReceiptQty = data.Sum(x => x.ReceiptQty),
            //             ReceiptUom = data.FirstOrDefault().ReceiptUom,
            //             RO = data.FirstOrDefault().RO,
            //             UNitCode = data.FirstOrDefault().UNitCode,
            //             UnitSenderCode = data.FirstOrDefault().UnitSenderCode,
            //             UnitRequestCode = data.FirstOrDefault().UnitRequestCode
            //         }).ToList();
            return(stock);
            //return SaldoAwal;
        }