public List <RouteBomIssueSP_Result> GetRouteBomIssue(string salesOrder, string style, ObservableCollection <string> styleColor, int operation, int direction, ObservableCollection <string> degree) { using (var context = new WorkFlowManagerDBEntities()) { context.CommandTimeout = 0; var routegroup = context.TblRouteGroups.Include("TblService1").FirstOrDefault(x => x.Iserial == operation); context.RouteBomIssues.MergeOption = MergeOption.NoTracking; var mainQuery = context.RouteBomIssueSP(salesOrder).ToList(); var a = mainQuery.Where(x => x.SalesOrderID == salesOrder && x.Style == style && styleColor.Contains(x.StyleColor) && (x.OperationIserial == operation) && x.BOM_IsLocalProduction != true && (x.ItemType == "Accessories" && direction == 1)).ToList(); if (direction == 0) { if (routegroup.SubFabricProcess == true) { var tempquery = context.RouteCardFabrics.Where( x => x.TblSalesOrder1.SalesOrderCode == salesOrder && x.RouteCardHeader.RoutGroupID == routegroup.Iserial); var rrrr = mainQuery.Where(x => x.SalesOrderID == salesOrder && x.Style == style && x.StyleColor.Contains(x.StyleColor) && x.Dyed == true && (x.ItemType == "Accessories" && direction == 0)).ToList(); var tradeagreement = context.TblTradeAgreementDetails.Where( x => (x.ItemCode == routegroup.TblService && x.ItemType == "Service")).Min(x => x.Price); var tblsalesorderbom = context.BOMs.Include("TblBOMSizes") .Include("TblBOMStyleColors.Tblcolor") .Include("TblBOMStyleColors.Tblcolor1") .Include("TblBOMStyleColors.Tblcolor2") .Include("TblSalesOrder1.TblSalesOrderColors") .Include("TblSalesOrder1.TblStyle1") .Where(x => x.Dyed == true && x.BOM_FabricType == "Accessories" && x.TblSalesOrder1.SalesOrderCode == salesOrder && x.TblSalesOrder1.Status == 1 && x.TblSalesOrder1.SalesOrderType == 2).ToList(); if (routegroup.SubFabricProcess == true) { foreach (var variable in rrrr) { var total = tempquery.Where(x => x.FabricColor == variable.FabricColorIserial && x.NewFabricColor == variable.DyedColor && x.ItemId == variable.ItemId && x.StyleColor == variable.StyleColorCode).Sum(x => x.Qty); variable.Total = total; if (!a.Any(x => x.FabricColorIserial == variable.FabricColorIserial && x.DyedColor == variable.DyedColor && x.ItemId == variable.ItemId && x.StyleColorCode == variable.StyleColorCode)) { a.Add(variable); } } } foreach (var bom in tblsalesorderbom) { double?total = 0; foreach (var variable in bom.TblBOMStyleColors.Where(x => x.DyedColor != null && styleColor.Contains(x.TblColor1.Code))) { if (bom.BOM_FabricType.StartsWith("Acc")) { var fabric = context.tbl_AccessoryAttributesHeader.FirstOrDefault(x => x.Iserial == bom.BOM_Fabric) .Code; total = tempquery.Where(x => x.FabricColor == variable.FabricColor && x.NewFabricColor == variable.DyedColor && x.ItemId == fabric && x.StyleColor == variable.StyleColor).Sum(x => x.Qty); } else { var fabric = context.tbl_FabricAttriputes.FirstOrDefault(x => x.Iserial == bom.BOM_Fabric) .FabricID; total = tempquery.Where(x => x.FabricColor == variable.FabricColor && x.NewFabricColor == variable.DyedColor && x.ItemId == fabric && x.StyleColor == variable.StyleColor).Sum(x => x.Qty); } a.Add(new RouteBomIssueSP_Result() { BOM_FabricType = "Service", Brand_Ename = bom.TblSalesOrder1.TblStyle1.Brand, FabricColor = variable.TblColor.Code, FabricColorIserial = variable.TblColor.Iserial, UnitID = "KG", StyleColor = variable.TblColor1.Code, StyleColorCode = variable.TblColor1.Iserial, DyedColor = variable.TblColor2.Iserial, DyedColorCode = variable.TblColor2.Code, ItemType = "Service", ItemId = routegroup.TblService1.Code, CostPerUnit = tradeagreement, MaterialUsage = bom.TblBOMSizes.Min(w => w.MaterialUsage), Total = total, SalesOrderID = salesOrder, SalesOrderIserial = bom.TblSalesOrder, Dyed = true, Style = bom.TblSalesOrder1.TblStyle1.StyleCode, StyleCode = bom.TblSalesOrder1.TblStyle1.StyleCode, }); } } } } else { var rrrr = mainQuery.Where(x => x.SalesOrderID == salesOrder && x.Style == style && styleColor.Contains(x.StyleColor) && (x.Dyed == true || x.BOM_IsLocalProduction == true) && (x.ItemType == "Accessories" && direction == 1)).ToList(); if (rrrr.Any(x => x.Dyed == true)) { var service = context.TblServices.FirstOrDefault(x => x.Iserial == 14); var tradeagreement = context.TblTradeAgreementDetails.Where( x => (x.ItemCode == service.Iserial && x.ItemType == "Service")).Min(x => x.Price); var tblsalesorderbom = context.BOMs.Include("TblBOMSizes") .Include("TblBOMStyleColors.TblColor") .Include("TblBOMStyleColors.TblColor1") .Include("TblBOMStyleColors.TblColor2") .Include("TblSalesOrder1.TblSalesOrderColors") .Include("TblSalesOrder1.TblStyle1") .Where(x => x.Dyed == true && x.BOM_FabricType == "Accessories" && x.TblSalesOrder1.SalesOrderCode == salesOrder && x.TblSalesOrder1.Status == 1 && x.TblSalesOrder1.SalesOrderType == 2).ToList(); if (routegroup.SubFabricProcess == true) { a.AddRange(rrrr); } if (routegroup.SubFabricProcess == false) { foreach (var bom in tblsalesorderbom) { foreach (var variable in bom.TblBOMStyleColors.Where(x => x.DyedColor != null && styleColor.Contains(x.TblColor1.Code))) { a.Add(new RouteBomIssueSP_Result() { BOM_FabricType = "Service", Brand_Ename = bom.TblSalesOrder1.TblStyle1.Brand, FabricColor = variable.TblColor.Code, FabricColorIserial = variable.TblColor.Iserial, StyleColor = variable.TblColor1.Code, StyleColorCode = variable.TblColor1.Iserial, DyedColor = variable.TblColor2.Iserial, DyedColorCode = variable.TblColor2.Code, ItemType = "Service", ItemId = service.Code, CostPerUnit = tradeagreement, MaterialUsage = bom.TblBOMSizes.Min(w => w.MaterialUsage), Total = bom.TblSalesOrder1.TblSalesOrderColors.FirstOrDefault(x => x.TblColor == variable.StyleColor).Total * bom.TblBOMSizes.Min(w => w.MaterialUsage), UnitID = "KG", SalesOrderID = salesOrder, SalesOrderIserial = bom.TblSalesOrder, Dyed = true, Style = bom.TblSalesOrder1.TblStyle1.StyleCode, StyleCode = bom.TblSalesOrder1.TblStyle1.StyleCode, }); } } } } else if (rrrr.Any(x => x.BOM_IsLocalProduction)) { var service = routegroup.TblService1; var tradeagreement = context.TblTradeAgreementDetails.Where( x => (x.ItemCode == service.Iserial && x.ItemType == "Service")).Min(x => x.Price); var tblsalesorderbom = context.BOMs.Include("TblBOMSizes").Include("BomFabricBoms") .Include("TblBOMStyleColors.TblColor") .Include("TblBOMStyleColors.TblColor1") .Include("TblBOMStyleColors.TblColor2") .Include("TblSalesOrder1.TblSalesOrderColors") .Include("TblSalesOrder1.TblStyle1") .Where(x => x.BOM_IsLocalProduction && x.BOM_FabricType == "Accessories" && x.TblSalesOrder1.SalesOrderCode == salesOrder && x.TblSalesOrder1.Status == 1 && x.TblSalesOrder1.SalesOrderType == 2).ToList(); foreach (var bom in tblsalesorderbom) { foreach (var variable in bom.TblBOMStyleColors.Where(x => styleColor.Contains(x.TblColor1.Code))) { a.Add(new RouteBomIssueSP_Result() { BOM_FabricType = "Service", Brand_Ename = bom.TblSalesOrder1.TblStyle1.Brand, FabricColor = variable.TblColor.Code, FabricColorIserial = variable.TblColor.Iserial, StyleColor = variable.TblColor1.Code, StyleColorCode = variable.TblColor1.Iserial, ItemType = "Service", ItemId = service.Code, CostPerUnit = tradeagreement, MaterialUsage = bom.TblBOMSizes.Min(w => w.MaterialUsage), Total = bom.TblSalesOrder1.TblSalesOrderColors.FirstOrDefault(x => x.TblColor == variable.StyleColor).Total * bom.TblBOMSizes.Min(w => w.MaterialUsage), UnitID = "KG", SalesOrderID = salesOrder, SalesOrderIserial = bom.TblSalesOrder, Style = bom.TblSalesOrder1.TblStyle1.StyleCode, StyleCode = bom.TblSalesOrder1.TblStyle1.StyleCode, }); foreach (var VARIABLE in bom.BomFabricBoms.Where(x => x.BOM_FabricRout == operation)) { var item = context.FabricAccSearches.FirstOrDefault( x => x.Iserial == VARIABLE.Item && x.ItemGroup == VARIABLE.ItemType); a.Add(new RouteBomIssueSP_Result() { BOM_FabricType = VARIABLE.ItemType, Brand_Ename = bom.TblSalesOrder1.TblStyle1.Brand, FabricColor = variable.TblColor.Code, FabricColorIserial = variable.TblColor.Iserial, StyleColor = variable.TblColor1.Code, StyleColorCode = variable.TblColor1.Iserial, ItemType = VARIABLE.ItemType, ItemId = item.Code, CostPerUnit = tradeagreement, MaterialUsage = bom.TblBOMSizes.Min(w => w.MaterialUsage), Total = bom.TblSalesOrder1.TblSalesOrderColors.FirstOrDefault(x => x.TblColor == variable.StyleColor).Total * bom.TblBOMSizes.Min(w => w.MaterialUsage), UnitID = item.Unit, SalesOrderID = salesOrder, SalesOrderIserial = bom.TblSalesOrder, Style = bom.TblSalesOrder1.TblStyle1.StyleCode, StyleCode = bom.TblSalesOrder1.TblStyle1.StyleCode, }); } } } } } return(a); } }