public ActionResult CreateConnectorPublicationRule(bool Publication) { var connectorPublicationRule = new ConnectorPublicationRule(); TryUpdateModel <ConnectorPublicationRule>(connectorPublicationRule); if (connectorPublicationRule.ConnectorID == 0) { return(Failure("Faild to save Connector Publication Rule. Connector does not exist")); } if (connectorPublicationRule.VendorID == 0) { return(Failure("Faild to save Connector Publication Rule. Vendor does not exist")); } connectorPublicationRule.PublicationType = Publication ? (int)ConnectorPublicationRuleType.Include : (int)ConnectorPublicationRuleType.Exclude; using (var unit = GetUnitOfWork()) { try { unit.Service <ConnectorPublicationRule>().Create(connectorPublicationRule); unit.Save(); return(Success("Successfully created Connector Publication Rule", isMultipartRequest: true, needsRefresh: false)); } catch (Exception e) { return(Failure("Failed to create Connector Publication Rule. ", e, isMultipartRequest: true)); } } }
public List <VendorProductInfo> GetListOfProductsByConnectorPublicationRule(ConnectorPublicationRule connectorpublicationRule) { string additionalFilters = String.Empty; string masterGroupMappingFilter = String.Empty; string baseQuery = @" SELECT VA.VendorID, VA.VendorAssortmentID, min(VP.ConcentratorStatusID) as ConcentratorStatusID, P.BrandID, VA.ProductID, ISNULL(SUM(VS.QuantityOnHand),0) AS QuantityOnHand, max(vp.costprice) as Price, {2} as ConnectorPublicationRuleID FROM VendorAssortment VA INNER JOIN Product P ON (VA.ProductID = P.ProductID) {3} INNER JOIN VendorStock VS ON (VA.VendorID = VS.VendorID AND VA.ProductID = VS.ProductID) INNER JOIN VendorPrice VP ON (VA.VendorAssortmentID = VP.VendorAssortmentID) WHERE va.IsActive = 1 AND VA.VendorID = {0} {1} GROUP BY VA.VendorID, VA.VendorAssortmentID, P.BrandID, VA.ProductID"; if (connectorpublicationRule.BrandID.HasValue) { additionalFilters += String.Format(" AND P.BrandID = {0}", connectorpublicationRule.BrandID); } if (connectorpublicationRule.MasterGroupMappingID.HasValue && connectorpublicationRule.MasterGroupMappingID.Value > 0) { masterGroupMappingFilter = String.Format("INNER JOIN dbo.MasterGroupMappingProduct mgmp ON p.ProductID = mgmp.ProductID"); additionalFilters += String.Format(" AND mgmp.MasterGroupMappingID = {0}", connectorpublicationRule.MasterGroupMappingID); } if (connectorpublicationRule.ProductID.HasValue) { additionalFilters += String.Format(" AND va.productid = {0}", connectorpublicationRule.ProductID); } if (connectorpublicationRule.PublishOnlyStock.HasValue && connectorpublicationRule.PublishOnlyStock.Value) { additionalFilters += " AND VS.QuantityOnHand > 0"; } if (connectorpublicationRule.StatusID.HasValue) { additionalFilters += String.Format(" AND VP.ConcentratorStatusID = {0}", connectorpublicationRule.StatusID.Value); } if (connectorpublicationRule.FromPrice.HasValue) { additionalFilters += String.Format(" AND vp.Price > {0}", connectorpublicationRule.FromPrice.Value.ToString(CultureInfo.InvariantCulture)); } if (connectorpublicationRule.ToPrice.HasValue) { additionalFilters += String.Format(" AND vp.Price < {0}", connectorpublicationRule.ToPrice.Value.ToString(CultureInfo.InvariantCulture)); } List <VendorProductInfo> products = petaPoco.Query <VendorProductInfo>(String.Format(baseQuery, connectorpublicationRule.VendorID, additionalFilters, connectorpublicationRule.ConnectorPublicationRuleID, masterGroupMappingFilter)).ToList(); return(products); }
public ActionResult UpdatePublicationRuleIndex(string ListOfConnectorPublicationRuleIDsJson) { var ListOfConnectorPublicationRuleIDsModel = new { listOfPublicationIndex = new[] { new { ConnectorPublicationRuleID = 0, ConnectorPublicationRuleIndex = 0 } } }; var ListOfIDs = JsonConvert.DeserializeAnonymousType(ListOfConnectorPublicationRuleIDsJson, ListOfConnectorPublicationRuleIDsModel); using (var unit = GetUnitOfWork()) { bool savePublicationIndex = true; ListOfIDs.listOfPublicationIndex.ForEach(x => { ConnectorPublicationRule connectorPublicationRule = unit .Service <ConnectorPublicationRule>() .Get(c => c.ConnectorPublicationRuleID == x.ConnectorPublicationRuleID); if (connectorPublicationRule != null) { connectorPublicationRule.PublicationIndex = x.ConnectorPublicationRuleIndex; } else { savePublicationIndex = false; } }); if (savePublicationIndex) { unit.Save(); return(Success("Successfully updated Connector Publication Rule Index")); } else { return(Failure("Faild to updated Connector Publication Rule Index")); } } }
private IEnumerable <VendorAssortment> GetVendorAssortmentForConnectorPublicationRule(ConnectorPublicationRule connectorPublicationRule) { var queryBuilder = new QueryBuilder() .From("[dbo].[VendorAssortment] AS [VA]") .Where("[VA].[VendorID] = @VendorID") .Where("[VA].[IsActive] = 1"); if (connectorPublicationRule.ProductID.HasValue) { queryBuilder.Where("[VA].[ProductID] = @ProductID"); } if (connectorPublicationRule.BrandID.HasValue) { queryBuilder .Join(JoinType.Inner, "[dbo].[Product] AS [P]", "[VA].[ProductID] = [P].[ProductID]") .Where("[P].[BrandID] = @BrandID"); } if (connectorPublicationRule.MasterGroupMappingID.HasValue && connectorPublicationRule.MasterGroupMappingID.Value > 0) { queryBuilder .Join(JoinType.Inner, "[dbo].[MasterGroupMappingProduct] AS [MGMP]", "[MGMP].[MasterGroupMappingID] = @MasterGroupMappingID AND [MGMP].[ProductID] = [VA].[ProductID]") .Where("MGMP.[IsProductMapped] = 1"); if (connectorPublicationRule.OnlyApprovedProducts) { queryBuilder .Join(JoinType.Inner, "[dbo].[MasterGroupMapping] AS [MGM]", "[MGMP].[MasterGroupMappingID] = [MGM].[MasterGroupMappingID]") .Where("[MGMP].[IsApproved] = 1") .Where("[MGM].[ConnectorID] IS NULL"); } } if (connectorPublicationRule.PublishOnlyStock.GetValueOrDefault()) { queryBuilder .Join(JoinType.Inner, "[dbo].[VendorStock] AS [VS]", "[VA].[ProductID] = VS.[ProductID] AND [VA].[VendorID] = [VS].[VendorID]") .Where("[VS].[QuantityOnHand] > 0"); } if (connectorPublicationRule.StatusID.HasValue || connectorPublicationRule.FromPrice.HasValue || connectorPublicationRule.ToPrice.HasValue) { queryBuilder.Join(JoinType.Inner, "[dbo].[VendorPrice] AS [VP]", "[VA].[VendorAssortmentID] = [VP].[VendorAssortmentID]"); if (connectorPublicationRule.StatusID.HasValue) { queryBuilder.Where("[VP].[ConcentratorStatusID] = @StatusID"); } if (connectorPublicationRule.FromPrice.HasValue) { queryBuilder.Where("[VP].[Price] >= @FromPrice"); } if (connectorPublicationRule.FromPrice.HasValue) { queryBuilder.Where("[VP].[Price] < @ToPrice"); } } if (connectorPublicationRule.AttributeID.HasValue || !String.IsNullOrEmpty(connectorPublicationRule.AttributeValue)) { queryBuilder .Join(JoinType.Inner, "[dbo].[ProductAttributeValue] AS [PAV]", "[VA].[ProductID] = [PAV].[ProductID] AND [VA].[VendorID] = [PAV].[VendorID]") .Where("[PAV].[AttributeID] = @AttributeID") .Where("[PAV].[Value] = @AttributeValue"); } return(Database .Query <VendorAssortment>(queryBuilder.Select("DISTINCT [VA].*"), connectorPublicationRule) .ToArray()); }
private List <VendorProductInfoWithWehkampInformation> GetProductsByConnectorPublicationRule(ConnectorPublicationRule connectorpublicationRule, Database database) { string additionalFilters = String.Empty; string masterGroupMappingFilter = String.Empty; string productAttributeValueFilter = String.Empty; string onlyApprovedProductsFilter = string.Empty; //Reminder: IsActive als connectorsetting of optie in de connectorpublicationrule opnemen! //Is o.a. voor Jumbo voor als die overgaat op MasterGroupMapping, dit staat al in de oude GenerateAssortment plugin string baseQuery = @" SELECT VA.VendorID, VA.VendorAssortmentID, min(VP.ConcentratorStatusID) as ConcentratorStatusID, P.BrandID, VA.ProductID, ISNULL(SUM(VS.QuantityOnHand),0) AS QuantityOnHand, max(vp.costprice) as Price, {2} as ConnectorPublicationRuleID, {4} as ConnectorID, Va.ShortDescription, VA.LongDescription, va.LineType, va.LedgerClass, va.productdesk, cast(va.extendedcatalog as nvarchar(200)) as extendedcatalog, isnull(PM.ProductMatchID, -1) as ProductMatchID, {5} as PublicationRuleIndex, pavSentToWehkamp.Value as 'SentToWehkamp', pavSentAsDummy.Value as 'SentToWehkampAsDummy' FROM VendorAssortment VA LEFT OUTER JOIN ProductAttributeValue pavSentToWehkamp ON va.ProductID = pavSentToWehkamp.ProductID AND pavSentToWehkamp.AttributeID = (SELECT AttributeID FROM ProductAttributeMetaData pamd WHERE pamd.AttributeCode = 'SentToWehkamp') LEFT OUTER JOIN ProductAttributeValue pavSentAsDummy ON va.ProductID = pavSentAsDummy.ProductID AND pavSentAsDummy.AttributeID = (SELECT AttributeID FROM ProductAttributeMetaData pamd WHERE pamd.AttributeCode = 'SentToWehkampAsDummy') INNER JOIN Product P ON (VA.ProductID = P.ProductID) {3} {6} LEFT JOIN VendorStock VS ON (VA.VendorID = VS.VendorID AND VA.ProductID = VS.ProductID) LEFT JOIN VendorPrice VP ON (VA.VendorAssortmentID = VP.VendorAssortmentID) {7} left join ProductMatch PM on PM.ProductID = VA.ProductID and pm.isMatched = 1 and pm.MatchStatus = 2 WHERE va.IsActive = 1 AND ((p.isconfigurable = 0 and VP.Vendorassortmentid is not null) or (p.isconfigurable = 1)) AND VA.VendorID = {0} {1} GROUP BY VA.VendorID, VA.VendorAssortmentID, P.BrandID, VA.ProductID,Va.ShortDescription, VA.LongDescription, va.LineType, va.LedgerClass, va.productdesk, va.extendedcatalog, PM.ProductMatchID,pavSentToWehkamp.Value,pavSentAsDummy.Value"; if (connectorpublicationRule.BrandID.HasValue) { additionalFilters += String.Format(" AND P.BrandID = {0}", connectorpublicationRule.BrandID); } if (connectorpublicationRule.MasterGroupMappingID.HasValue && connectorpublicationRule.MasterGroupMappingID.Value > 0) { masterGroupMappingFilter = String.Format("INNER JOIN dbo.MasterGroupMappingProduct mgmp ON p.ProductID = mgmp.ProductID and mgmp.IsProductMapped = 1"); additionalFilters += String.Format(" AND mgmp.MasterGroupMappingID = {0}", connectorpublicationRule.MasterGroupMappingID); } if (connectorpublicationRule.ProductID.HasValue) { additionalFilters += String.Format(" AND va.productid = {0}", connectorpublicationRule.ProductID); } if (connectorpublicationRule.PublishOnlyStock.HasValue && connectorpublicationRule.PublishOnlyStock.Value) { additionalFilters += " AND VS.QuantityOnHand > 0"; } if (connectorpublicationRule.StatusID.HasValue) { additionalFilters += String.Format(" AND VP.ConcentratorStatusID = {0}", connectorpublicationRule.StatusID.Value); } if (connectorpublicationRule.FromPrice.HasValue) { additionalFilters += String.Format(" AND vp.Price > {0}", connectorpublicationRule.FromPrice.Value.ToString(CultureInfo.InvariantCulture)); } if (connectorpublicationRule.ToPrice.HasValue) { additionalFilters += String.Format(" AND vp.Price < {0}", connectorpublicationRule.ToPrice.Value.ToString(CultureInfo.InvariantCulture)); } if (connectorpublicationRule.AttributeID.HasValue || !string.IsNullOrEmpty(connectorpublicationRule.AttributeValue)) { productAttributeValueFilter = String.Format("INNER JOIN dbo.ProductAttributeValue PAV ON VA.ProductID = PAV.ProductID"); if (connectorpublicationRule.AttributeID.HasValue) { additionalFilters += String.Format(" AND PAV.AttributeID = {0}", connectorpublicationRule.AttributeID); } if (!string.IsNullOrEmpty(connectorpublicationRule.AttributeValue)) { additionalFilters += String.Format(" AND PAV.Value = '{0}'", connectorpublicationRule.AttributeValue); } } if (connectorpublicationRule.OnlyApprovedProducts) { onlyApprovedProductsFilter = @" INNER JOIN dbo.MasterGroupMappingProduct mp ON P.ProductID = mp.ProductID AND mp.IsApproved = 1 AND mp.IsProductMapped = 1 INNER JOIN dbo.MasterGroupMapping m ON mp.MasterGroupMappingID = m.MasterGroupMappingID AND m.ConnectorID IS NULL "; } List <VendorProductInfoWithWehkampInformation> products = database.Query <VendorProductInfoWithWehkampInformation>(String.Format(baseQuery, connectorpublicationRule.VendorID, additionalFilters, connectorpublicationRule.ConnectorPublicationRuleID, masterGroupMappingFilter, connectorpublicationRule.ConnectorID, connectorpublicationRule.PublicationIndex, productAttributeValueFilter, onlyApprovedProductsFilter)).ToList(); return(products); }