Example #1
0
        public IEnumerable <JobInvoiceRateAmendmentLineViewModel> GetJobInvoiceLineForMultiSelect(JobInvoiceAmendmentFilterViewModel svm)
        {
            var Header = db.JobInvoiceAmendmentHeader.Find(svm.JobInvoiceAmendmentHeaderId);

            var Settings = new JobInvoiceSettingsService(_unitOfWork).GetJobInvoiceSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.SiteId);

            string[] contraDocTypes = null;
            if (!string.IsNullOrEmpty(Settings.filterContraDocTypes))
            {
                contraDocTypes = Settings.filterContraDocTypes.Split(",".ToCharArray());
            }
            else
            {
                contraDocTypes = new string[] { "NA" };
            }

            string[] contraSites = null;
            if (!string.IsNullOrEmpty(Settings.filterContraSites))
            {
                contraSites = Settings.filterContraSites.Split(",".ToCharArray());
            }
            else
            {
                contraSites = new string[] { "NA" };
            }

            string[] contraDivisions = null;
            if (!string.IsNullOrEmpty(Settings.filterContraDivisions))
            {
                contraDivisions = Settings.filterContraDivisions.Split(",".ToCharArray());
            }
            else
            {
                contraDivisions = new string[] { "NA" };
            }


            string[] ProductIdArr = null;
            if (!string.IsNullOrEmpty(svm.ProductId))
            {
                ProductIdArr = svm.ProductId.Split(",".ToCharArray());
            }
            else
            {
                ProductIdArr = new string[] { "NA" };
            }

            string[] SaleOrderIdArr = null;
            if (!string.IsNullOrEmpty(svm.JobInvoiceId))
            {
                SaleOrderIdArr = svm.JobInvoiceId.Split(",".ToCharArray());
            }
            else
            {
                SaleOrderIdArr = new string[] { "NA" };
            }

            string[] ProductGroupIdArr = null;
            if (!string.IsNullOrEmpty(svm.ProductGroupId))
            {
                ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray());
            }
            else
            {
                ProductGroupIdArr = new string[] { "NA" };
            }

            string[] Dim1Id = null;
            if (!string.IsNullOrEmpty(svm.ProductGroupId))
            {
                Dim1Id = svm.Dimension1Id.Split(",".ToCharArray());
            }
            else
            {
                Dim1Id = new string[] { "NA" };
            }

            string[] Dim2Id = null;
            if (!string.IsNullOrEmpty(svm.ProductGroupId))
            {
                Dim2Id = svm.Dimension2Id.Split(",".ToCharArray());
            }
            else
            {
                Dim2Id = new string[] { "NA" };
            }

            string[] Dim3Id = null;
            if (!string.IsNullOrEmpty(svm.ProductGroupId))
            {
                Dim3Id = svm.Dimension3Id.Split(",".ToCharArray());
            }
            else
            {
                Dim3Id = new string[] { "NA" };
            }

            string[] Dim4Id = null;
            if (!string.IsNullOrEmpty(svm.ProductGroupId))
            {
                Dim4Id = svm.Dimension4Id.Split(",".ToCharArray());
            }
            else
            {
                Dim4Id = new string[] { "NA" };
            }

            var Query = (from VJ in db.ViewJobInvoiceBalanceForRateAmendment
                         join p in db.JobInvoiceLine on VJ.JobInvoiceLineId equals p.JobInvoiceLineId
                         join t2 in db.JobInvoiceHeader on p.JobInvoiceHeaderId equals t2.JobInvoiceHeaderId
                         join JR in db.JobReceiveLine on p.JobReceiveLineId equals JR.JobReceiveLineId
                         join JO in db.JobOrderLine on JR.JobOrderLineId equals JO.JobOrderLineId
                         join product in db.Product on JO.ProductId equals product.ProductId into table2
                         from tab2 in table2.DefaultIfEmpty()
                         join AL in db.JobInvoiceRateAmendmentLine on p.JobInvoiceLineId equals AL.JobInvoiceLineId into ALTable
                         from AlTab in ALTable.DefaultIfEmpty()
                         where
                         //(string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(JO.ProductId.ToString()))
                         //&& (string.IsNullOrEmpty(svm.JobInvoiceId) ? 1 == 1 : SaleOrderIdArr.Contains(p.JobInvoiceHeaderId.ToString()))
                         //&& (string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString()))
                         //&& (string.IsNullOrEmpty(svm.Dimension1Id) ? 1 == 1 : Dim1Id.Contains(JO.Dimension1Id.ToString()))
                         //&& (string.IsNullOrEmpty(svm.Dimension2Id) ? 1 == 1 : Dim2Id.Contains(JO.Dimension2Id.ToString()))
                         //&& (string.IsNullOrEmpty(Settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(t2.DocTypeId.ToString()))
                         //&& (string.IsNullOrEmpty(Settings.filterContraDivisions) ? t2.DivisionId == Header.DivisionId : contraDivisions.Contains(t2.DivisionId.ToString()))
                         //&& (string.IsNullOrEmpty(Settings.filterContraSites) ? t2.SiteId == Header.SiteId : contraSites.Contains(t2.SiteId.ToString()))
                         //&& ((svm.JobWorkerId.HasValue && svm.JobWorkerId.Value > 0) ? p.JobWorkerId == svm.JobWorkerId : 1 == 1)
                         //&& ((svm.OldRate > 0) ? p.Rate == svm.OldRate : 1 == 1)
                         //&& (svm.UpToDate.HasValue ? t2.DocDate <= svm.UpToDate : 1 == 1)
                         //&&
                         p.Qty > 0 && t2.ProcessId == Header.ProcessId
                         orderby t2.DocDate, t2.DocNo, p.Sr
                         select new
            {
                Dimension1Name = JO.Dimension1.Dimension1Name,
                Dimension2Name = JO.Dimension2.Dimension2Name,
                Dimension3Name = JO.Dimension3.Dimension3Name,
                Dimension4Name = JO.Dimension4.Dimension4Name,
                UnitName = tab2.Unit.UnitName,
                DealUnitName = p.DealUnit.UnitName,
                DealQty = (p.Qty) * p.UnitConversionMultiplier,
                UnitConversionMultiplier = p.UnitConversionMultiplier,
                JobInvoiceRate = p.Rate,
                AmendedRate = (svm.Rate == 0 ? p.Rate : svm.Rate),
                Qty = (p.Qty),
                JobInvoiceDocNo = t2.DocNo,
                ProductName = tab2.ProductName,
                ProductId = tab2.ProductId,
                JobInvoiceAmendmentHeaderId = svm.JobInvoiceAmendmentHeaderId,
                JobInvoiceLineId = p.JobInvoiceLineId,
                unitDecimalPlaces = tab2.Unit.DecimalPlaces,
                DealunitDecimalPlaces = p.DealUnit.DecimalPlaces,
                JobWorkerId = p.JobWorkerId,
                AAmended = (AlTab == null ? false : true),
                JobInvoiceHeaderId = p.JobInvoiceHeaderId,
                ProductGroupId = tab2.ProductGroupId,
                Dimension1Id = JO.Dimension1Id,
                Dimension2Id = JO.Dimension2Id,
                Dimension3Id = JO.Dimension3Id,
                Dimension4Id = JO.Dimension4Id,
                DocTypeId = t2.DocTypeId,
                DivisionId = t2.DivisionId,
                SiteId = t2.SiteId,
                DocDate = t2.DocDate,
            });

            if (!string.IsNullOrEmpty(svm.ProductId))
            {
                Query = Query.Where(m => ProductIdArr.Contains(m.ProductId.ToString()));
            }

            if (!string.IsNullOrEmpty(svm.JobInvoiceId))
            {
                Query = Query.Where(m => SaleOrderIdArr.Contains(m.JobInvoiceHeaderId.ToString()));
            }

            if (!string.IsNullOrEmpty(svm.ProductGroupId))
            {
                Query = Query.Where(m => ProductGroupIdArr.Contains(m.ProductGroupId.ToString()));
            }

            if (!string.IsNullOrEmpty(svm.Dimension1Id))
            {
                Query = Query.Where(m => Dim1Id.Contains(m.Dimension1Id.ToString()));
            }

            if (!string.IsNullOrEmpty(svm.Dimension2Id))
            {
                Query = Query.Where(m => Dim2Id.Contains(m.Dimension2Id.ToString()));
            }

            if (!string.IsNullOrEmpty(svm.Dimension3Id))
            {
                Query = Query.Where(m => Dim3Id.Contains(m.Dimension3Id.ToString()));
            }

            if (!string.IsNullOrEmpty(svm.Dimension4Id))
            {
                Query = Query.Where(m => Dim4Id.Contains(m.Dimension4Id.ToString()));
            }

            if (!string.IsNullOrEmpty(Settings.filterContraSites))
            {
                Query = Query.Where(m => contraSites.Contains(m.SiteId.ToString()));
            }
            else
            {
                Query = Query.Where(m => m.SiteId == Header.SiteId);
            }
            if (!string.IsNullOrEmpty(Settings.filterContraDivisions))
            {
                Query = Query.Where(m => contraDivisions.Contains(m.DivisionId.ToString()));
            }
            else
            {
                Query = Query.Where(m => m.DivisionId == Header.DivisionId);
            }

            if (!string.IsNullOrEmpty(Settings.filterContraDocTypes))
            {
                Query = Query.Where(m => contraDocTypes.Contains(m.DocTypeId.ToString()));
            }

            if (svm.OldRate > 0)
            {
                Query = Query.Where(m => m.JobInvoiceRate == svm.OldRate);
            }

            if (svm.JobWorkerId.HasValue && svm.JobWorkerId.Value > 0)
            {
                Query = Query.Where(m => m.JobWorkerId == svm.JobWorkerId);
            }

            if (svm.UpToDate.HasValue)
            {
                Query = Query.Where(m => m.DocDate <= svm.UpToDate);
            }


            return(from p in Query
                   select new JobInvoiceRateAmendmentLineViewModel
            {
                Dimension1Name = p.Dimension1Name,
                Dimension2Name = p.Dimension2Name,
                Dimension3Name = p.Dimension3Name,
                Dimension4Name = p.Dimension4Name,
                UnitName = p.UnitName,
                DealUnitName = p.UnitName,
                DealQty = p.DealQty,
                UnitConversionMultiplier = p.UnitConversionMultiplier,
                JobInvoiceRate = p.JobInvoiceRate,
                AmendedRate = p.AmendedRate,
                Qty = (p.Qty),
                JobInvoiceDocNo = p.JobInvoiceDocNo,
                ProductName = p.ProductName,
                ProductId = p.ProductId,
                JobInvoiceAmendmentHeaderId = p.JobInvoiceAmendmentHeaderId,
                JobInvoiceLineId = p.JobInvoiceLineId,
                unitDecimalPlaces = p.unitDecimalPlaces,
                DealunitDecimalPlaces = p.DealunitDecimalPlaces,
                JobWorkerId = p.JobWorkerId,
                AAmended = p.AAmended,
            });
        }
Example #2
0
        public IEnumerable <PurchaseOrderRateAmendmentLineViewModel> GetPurchaseOrderLineForMultiSelect(PurchaseOrderAmendmentFilterViewModel svm)
        {
            string[] ProductIdArr = null;
            if (!string.IsNullOrEmpty(svm.ProductId))
            {
                ProductIdArr = svm.ProductId.Split(",".ToCharArray());
            }
            else
            {
                ProductIdArr = new string[] { "NA" };
            }

            string[] SaleOrderIdArr = null;
            if (!string.IsNullOrEmpty(svm.PurchaseOrderId))
            {
                SaleOrderIdArr = svm.PurchaseOrderId.Split(",".ToCharArray());
            }
            else
            {
                SaleOrderIdArr = new string[] { "NA" };
            }

            string[] ProductGroupIdArr = null;
            if (!string.IsNullOrEmpty(svm.ProductGroupId))
            {
                ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray());
            }
            else
            {
                ProductGroupIdArr = new string[] { "NA" };
            }

            string[] Dim1Id = null;
            if (!string.IsNullOrEmpty(svm.ProductGroupId))
            {
                Dim1Id = svm.ProductGroupId.Split(",".ToCharArray());
            }
            else
            {
                Dim1Id = new string[] { "NA" };
            }

            string[] Dim2Id = null;
            if (!string.IsNullOrEmpty(svm.ProductGroupId))
            {
                Dim2Id = svm.ProductGroupId.Split(",".ToCharArray());
            }
            else
            {
                Dim2Id = new string[] { "NA" };
            }

            var temp = (from p in db.ViewPurchaseOrderBalanceForInvoice
                        join t3 in db.PurchaseOrderLine on p.PurchaseOrderLineId equals t3.PurchaseOrderLineId into table3
                        from tab3 in table3.DefaultIfEmpty()
                        join t2 in db.PurchaseOrderHeader on p.PurchaseOrderHeaderId equals t2.PurchaseOrderHeaderId
                        join product in db.Product on p.ProductId equals product.ProductId into table2
                        from tab2 in table2.DefaultIfEmpty()
                        join AL in db.PurchaseOrderRateAmendmentLine on p.PurchaseOrderLineId equals AL.PurchaseOrderLineId into ALTable
                        from AlTab in ALTable.DefaultIfEmpty()
                        where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
                        (string.IsNullOrEmpty(svm.PurchaseOrderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.PurchaseOrderHeaderId.ToString())) &&
                        (string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
                        (string.IsNullOrEmpty(svm.Dimension1Id) ? 1 == 1 : Dim1Id.Contains(p.Dimension1Id.ToString())) &&
                        (string.IsNullOrEmpty(svm.Dimension2Id) ? 1 == 1 : Dim2Id.Contains(p.Dimension2Id.ToString())) &&
                        p.BalanceQty > 0 && ((svm.SupplierId.HasValue && svm.SupplierId.Value > 0) ? p.SupplierId == svm.SupplierId : 1 == 1) &&
                        (svm.UpToDate.HasValue ? t2.DocDate <= svm.UpToDate : 1 == 1)
                        orderby t2.DocDate, t2.DocNo, tab3.Sr
                        select new PurchaseOrderRateAmendmentLineViewModel
            {
                Dimension1Name = tab3.Dimension1.Dimension1Name,
                Dimension2Name = tab3.Dimension2.Dimension2Name,
                UnitName = tab2.Unit.UnitName,
                DealUnitName = tab3.DealUnit.UnitName,
                DealQty = p.BalanceQty * tab3.UnitConversionMultiplier,
                UnitConversionMultiplier = tab3.UnitConversionMultiplier,
                PurchaseOrderRate = p.Rate,
                AmendedRate = (svm.Rate == 0 ? p.Rate : svm.Rate),
                Qty = p.BalanceQty,
                PurchaseOrderDocNo = p.PurchaseOrderNo,
                ProductName = tab2.ProductName,
                ProductId = p.ProductId,
                PurchaseOrderAmendmentHeaderId = svm.PurchaseOrderAmendmentHeaderId,
                PurchaseOrderLineId = p.PurchaseOrderLineId,
                unitDecimalPlaces = tab2.Unit.DecimalPlaces,
                dealUnitDecimalPlaces = tab3.DealUnit.DecimalPlaces,
                AAmended = (AlTab == null ? false : true)
            }
                        );

            return(temp);
        }