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);
}
