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, }); }
public IEnumerable <JobOrderRateAmendmentLineViewModel> GetJobOrderLineForMultiSelect(JobOrderAmendmentFilterViewModel 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.JobOrderId)) { SaleOrderIdArr = svm.JobOrderId.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.Dimension1Id)) { Dim1Id = svm.Dimension1Id.Split(",".ToCharArray()); } else { Dim1Id = new string[] { "NA" }; } string[] Dim2Id = null; if (!string.IsNullOrEmpty(svm.Dimension2Id)) { Dim2Id = svm.Dimension2Id.Split(",".ToCharArray()); } else { Dim2Id = new string[] { "NA" }; } string[] Dim3Id = null; if (!string.IsNullOrEmpty(svm.Dimension3Id)) { Dim3Id = svm.Dimension3Id.Split(",".ToCharArray()); } else { Dim3Id = new string[] { "NA" }; } string[] Dim4Id = null; if (!string.IsNullOrEmpty(svm.Dimension4Id)) { Dim4Id = svm.Dimension4Id.Split(",".ToCharArray()); } else { Dim4Id = new string[] { "NA" }; } var temp = (from p in db.ViewJobOrderBalanceForInvoice join t3 in db.JobOrderLine on p.JobOrderLineId equals t3.JobOrderLineId into table3 from tab3 in table3.DefaultIfEmpty() join t2 in db.JobOrderHeader on p.JobOrderHeaderId equals t2.JobOrderHeaderId join product in db.Product on p.ProductId equals product.ProductId into table2 from tab2 in table2.DefaultIfEmpty() join AL in db.JobOrderRateAmendmentLine on p.JobOrderLineId equals AL.JobOrderLineId into ALTable from AlTab in ALTable.DefaultIfEmpty() where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) && (string.IsNullOrEmpty(svm.JobOrderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.JobOrderHeaderId.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())) && (string.IsNullOrEmpty(svm.Dimension3Id) ? 1 == 1 : Dim3Id.Contains(p.Dimension3Id.ToString())) && (string.IsNullOrEmpty(svm.Dimension4Id) ? 1 == 1 : Dim4Id.Contains(p.Dimension4Id.ToString())) && p.BalanceQty > 0 && ((svm.JobWorkerId.HasValue && svm.JobWorkerId.Value > 0) ? p.JobWorkerId == svm.JobWorkerId : 1 == 1) && (svm.UpToDate.HasValue ? t2.DocDate <= svm.UpToDate : 1 == 1) orderby t2.DocDate, t2.DocNo, tab3.Sr select new JobOrderRateAmendmentLineViewModel { Dimension1Name = tab3.Dimension1.Dimension1Name, Dimension2Name = tab3.Dimension2.Dimension2Name, Dimension3Name = tab3.Dimension3.Dimension3Name, Dimension4Name = tab3.Dimension4.Dimension4Name, UnitName = tab2.Unit.UnitName, DealUnitName = tab3.DealUnit.UnitName, DealQty = p.BalanceQty * tab3.UnitConversionMultiplier, UnitConversionMultiplier = tab3.UnitConversionMultiplier, JobOrderRate = p.Rate, AmendedRate = (svm.Rate == 0 ? p.Rate : svm.Rate), Qty = p.BalanceQty, JobOrderDocNo = p.JobOrderNo, ProductName = tab2.ProductName, ProductId = p.ProductId, JobOrderAmendmentHeaderId = svm.JobOrderAmendmentHeaderId, JobOrderLineId = p.JobOrderLineId, unitDecimalPlaces = tab2.Unit.DecimalPlaces, DealunitDecimalPlaces = tab3.DealUnit.DecimalPlaces, JobWorkerId = p.JobWorkerId, AAmended = (AlTab == null ? false : true) } ); return(temp); }