Hi,

Can you show the custom code for PriceServiceDatabase to filter prices? 

Modifying an item in CM will pretty much clear all the versions - but that should not be related to the prices.

Regards.

/Q

Hello, yea sure: PriceDetailDatabase:

using EPiServer.Core;
using EPiServer.ServiceLocation;
using Mediachase.Commerce;
using Mediachase.Commerce.Catalog;
using Mediachase.Commerce.DateTimeConverters;
using Mediachase.Commerce.Pricing;
using Mediachase.Data.Provider;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace Nemetos.Galenicare.Framework.Helpers
{
[ServiceConfiguration(typeof(IPriceDetailService))]
public class PriceDetailDatabase : IPriceDetailService
{
private static Func<string> _defaultConnectionStringFunction = (Func<string>)(() => CatalogConfiguration.Instance.Connection.AppDatabase);
private static Func<string> _connectionStringFunction = (Func<string>)null;
private const string _spGet = "ecf_PriceDetail_Get";
private const string _spList = "ecf_PriceDetail_List";
private const string _spSave = "ecf_PriceDetail_Save";
private const string _spReplacePrices = "ecf_PriceDetail_ReplacePrices";
private ReferenceConverter _referenceConverter;
public Injected<IPriceService> priceService;

public static Func<string> ConnectionStringFunction
{
get
{
return PriceDetailDatabase._connectionStringFunction ?? PriceDetailDatabase._defaultConnectionStringFunction;
}
set
{
PriceDetailDatabase._connectionStringFunction = value;
}
}

public bool IsReadOnly
{
get
{
return false;
}
}

public PriceDetailDatabase(ReferenceConverter referenceConverter)
{
this._referenceConverter = referenceConverter;
}

public IPriceDetailValue Get(long priceValueId)
{
IPriceDetailValue priceDetailValue;
using (SqlConnection sqlConnection = new SqlConnection(PriceDetailDatabase.ConnectionStringFunction()))
{
sqlConnection.Open();
SqlCommand sqlCommand1 = new SqlCommand();
sqlCommand1.CommandType = CommandType.StoredProcedure;
sqlCommand1.CommandText = "ecf_PriceDetail_Get";
sqlCommand1.Connection = sqlConnection;
SqlCommand sqlCommand2 = sqlCommand1;
sqlCommand2.Parameters.Add(new SqlParameter("priceValueId", (object)priceValueId));
using (SqlDataReader sqlDataReader = sqlCommand2.ExecuteReader())
{
if (!sqlDataReader.Read())
{
priceDetailValue = (IPriceDetailValue)null;
}
else
{
priceDetailValue = this.ReadPriceDetailValue((IDataReader)sqlDataReader);
if (sqlDataReader.Read())
throw new Exception("Multiple results returned from Get.");
}
}
}
return priceDetailValue;
}

public IList<IPriceDetailValue> List(ContentReference catalogContentReference)
{
int totalCount;
return (IList<IPriceDetailValue>)this.ListWorker(catalogContentReference, MarketId.Empty, new PriceFilter(), new int?(), new int?(), out totalCount);
}

public IList<IPriceDetailValue> List(ContentReference catalogContentReference, MarketId marketId, PriceFilter priceFilter, int offset, int count, out int totalCount)
{
return (IList<IPriceDetailValue>)this.ListWorker(catalogContentReference, marketId, priceFilter, new int?(offset), new int?(count), out totalCount);
}

public IList<IPriceDetailValue> List(ContentReference catalogContentReference, int offset, int count, out int totalCount)
{
return (IList<IPriceDetailValue>)this.ListWorker(catalogContentReference, MarketId.Empty, new PriceFilter(), new int?(offset), new int?(count), out totalCount);
}

public IList<IPriceDetailValue> Save(IEnumerable<IPriceDetailValue> priceValues)
{
return (IList<IPriceDetailValue>)this.SaveWorker(TableTypeSerializers.CreatePriceDetailTable(priceValues, (IEnumerable<long>)null));
}

public void Delete(IEnumerable<long> priceValueIds)
{
this.SaveWorker(TableTypeSerializers.CreatePriceDetailTable((IEnumerable<IPriceDetailValue>)null, priceValueIds));
}

public void ReplicatePriceServiceChanges(IEnumerable<CatalogKey> catalogKeys, IEnumerable<IPriceValue> priceValues)
{
TableTypeSerializers.CreateCatalogKeysTable(catalogKeys);
TableTypeSerializers.CreatePriceValuesTable(priceValues);
DataCommand command = new DataCommand()
{
ConnectionString = PriceDetailDatabase.ConnectionStringFunction(),
CommandType = CommandType.StoredProcedure,
CommandText = "ecf_PriceDetail_ReplacePrices"
};
command.Parameters.Add(new DataParameter("CatalogKeys", (object)TableTypeSerializers.CreateCatalogKeysTable(catalogKeys)));
command.Parameters.Add(new DataParameter("PriceValues", (object)TableTypeSerializers.CreatePriceValuesTable(priceValues)));
DataService.ExecuteNonExec(command);
}

private List<IPriceDetailValue> ListWorker(ContentReference catalogContentReference, MarketId marketId, PriceFilter filter, int? offset, int? count, out int totalCount)
{
int objectId = this._referenceConverter.GetObjectId(catalogContentReference);
CatalogContentType contentType = this._referenceConverter.GetContentType(catalogContentReference);
List<IPriceDetailValue> list;
using (SqlConnection sqlConnection = new SqlConnection(PriceDetailDatabase.ConnectionStringFunction()))
{
sqlConnection.Open();
SqlCommand sqlCommand1 = new SqlCommand();
sqlCommand1.CommandType = CommandType.StoredProcedure;
sqlCommand1.CommandText = "ecf_PriceDetail_List";
sqlCommand1.Connection = sqlConnection;
SqlCommand sqlCommand2 = sqlCommand1;
sqlCommand2.Parameters.Add(new SqlParameter("catalogEntryId", (object)(contentType == CatalogContentType.CatalogEntry ? new int?(objectId) : new int?())));
sqlCommand2.Parameters.Add(new SqlParameter("catalogNodeId", (object)(contentType == CatalogContentType.CatalogNode ? new int?(objectId) : new int?())));
sqlCommand2.Parameters.Add(new SqlParameter("MarketId", (object)marketId.Value));
sqlCommand2.Parameters.Add(new SqlParameter("CurrencyCodes", (object)TableTypeSerializers.CreateCurrencyCodesTable(filter.Currencies)));
sqlCommand2.Parameters.Add(new SqlParameter("CustomerPricing", (object)TableTypeSerializers.CreateCustomerPricingTable(filter.CustomerPricing)));
SqlParameterCollection parameters = sqlCommand2.Parameters;
SqlParameter sqlParameter1 = new SqlParameter();
sqlParameter1.ParameterName = "totalCount";
sqlParameter1.Direction = ParameterDirection.Output;
sqlParameter1.SqlDbType = SqlDbType.Int;
SqlParameter sqlParameter2 = sqlParameter1;
parameters.Add(sqlParameter2);
sqlCommand2.Parameters.Add(new SqlParameter("pagingOffset", (object)offset));
sqlCommand2.Parameters.Add(new SqlParameter("pagingCount", (object)count));
string market = GetUserMarketEligiblility();
using (SqlDataReader sqlDataReader = sqlCommand2.ExecuteReader())
{
list = new List<IPriceDetailValue>();
while (sqlDataReader.Read())
{
IPriceDetailValue priceDetailValue = this.ReadPriceDetailValue((IDataReader)sqlDataReader);
if(priceDetailValue.MarketId.Value.ToLower()==market.ToLower() || market.Equals("ALL"))
list.Add(priceDetailValue);
}
}
totalCount = (int)sqlCommand2.Parameters["totalCount"].Value;
}
return list;
}
public string GetUserMarketEligiblility()
{
string market = string.Empty;
bool isSunstore = System.Web.Security.Roles.IsUserInRole("SunstoreGrp");
bool isAmavita = System.Web.Security.Roles.IsUserInRole("AmavitaGrp");
bool isCoop = System.Web.Security.Roles.IsUserInRole("CoopGrp");
bool isAdmin = System.Web.Security.Roles.IsUserInRole("Administrators");
if (!isAdmin)
{
if (isSunstore)
{
market = "DEFAULT";
}
if (isAmavita)
{
market = "Amavita";
}
if (isCoop)
{
market = "Coop";
}
if (string.IsNullOrEmpty(market))
{
market = "DEFAULT";
}
}
else
{
market = "ALL";
}

return market;
}
private List<IPriceDetailValue> SaveWorker(DataTable priceTable)
{
SqlConnection sqlConnection = (SqlConnection)null;
List<IPriceDetailValue> list1 = (List<IPriceDetailValue>)null;
try
{
using (TransactionScope transactionScope = new TransactionScope())
{
SqlCommand sqlCommand1 = new SqlCommand();
sqlCommand1.CommandType = CommandType.StoredProcedure;
sqlCommand1.CommandText = "ecf_PriceDetail_Save";
SqlCommand sqlCommand2 = sqlCommand1;
sqlCommand2.Parameters.Add(new SqlParameter("priceValues", (object)priceTable));
if (TransactionScope.OpenConnection((IDbCommand)sqlCommand2, PriceDetailDatabase.ConnectionStringFunction(), (TransactionScope.ConnectionDelegate)(() => (IDbConnection)new SqlConnection())))
sqlConnection = sqlCommand2.Connection;
HashSet<CatalogKey> hashSet = new HashSet<CatalogKey>();
List<IPriceDetailValue> list2 = new List<IPriceDetailValue>();
list1 = new List<IPriceDetailValue>();
string market = GetUserMarketEligiblility();
string currentMarket = string.Empty;
using (SqlDataReader sqlDataReader = sqlCommand2.ExecuteReader())
{
while (sqlDataReader.Read())
{
IPriceDetailValue priceDetailValue = this.ReadPriceDetailValue((IDataReader)sqlDataReader);
currentMarket = priceDetailValue.MarketId.Value;
if (market.ToLower() == currentMarket.ToLower() || market == "ALL")
list1.Add(priceDetailValue);
}
sqlDataReader.NextResult();
while (sqlDataReader.Read())
{
if (sqlDataReader["PriceValueId"] == DBNull.Value)
{
Guid applicationId = (Guid)sqlDataReader["ApplicationId"];
string catalogEntryCode = (string)sqlDataReader["CatalogEntryCode"];
hashSet.Add(new CatalogKey(applicationId, catalogEntryCode));
}
else
{
IPriceDetailValue priceDetailValue = this.ReadPriceDetailValue((IDataReader)sqlDataReader);
hashSet.Add(priceDetailValue.CatalogKey);
list2.Add(priceDetailValue);
}
}
}

//DELETE
bool isDelete = true;
foreach (DataRow row in priceTable.Rows)
{
if (row["CatalogEntryCode"] != System.DBNull.Value)
{
isDelete = false;
}
}

if (market.ToLower() == currentMarket.ToLower() || market == "ALL" || isDelete)
{
this.priceService.Service.ReplicatePriceDetailChanges((IEnumerable<CatalogKey>)hashSet, (IEnumerable<IPriceValue>)list2);
transactionScope.Complete();
}
}
}
finally
{
if (sqlConnection != null)
sqlConnection.Dispose();
}
return list1;
}

private IPriceDetailValue ReadPriceDetailValue(IDataReader reader)
{
PriceDetailValue priceDetailValue = new PriceDetailValue();
priceDetailValue.PriceValueId = (long)reader["PriceValueId"];
priceDetailValue.CatalogKey = new CatalogKey((Guid)reader["ApplicationId"], (string)reader["CatalogEntryCode"]);
priceDetailValue.MarketId = new MarketId((string)reader["MarketId"]);
priceDetailValue.CustomerPricing = new CustomerPricing((CustomerPricing.PriceType)reader["PriceTypeId"], (string)reader["PriceCode"]);
priceDetailValue.ValidFrom = DateTimeHandling.ConvertDatabaseValue(reader["ValidFrom"]).Value;
priceDetailValue.ValidUntil = DateTimeHandling.ConvertDatabaseValue(reader["ValidUntil"]);
priceDetailValue.MinQuantity = (Decimal)reader["MinQuantity"];
priceDetailValue.UnitPrice = new Money((Decimal)reader["UnitPrice"], (Mediachase.Commerce.Currency)((string)reader["CurrencyCode"]));
return (IPriceDetailValue)priceDetailValue;
}
}
}

PriceServiceDatabase:

using EPiServer.Events.ChangeNotification;
using EPiServer.Framework.Cache;
using EPiServer.Personalization;
using EPiServer.ServiceLocation;
using Mediachase.Commerce;
using Mediachase.Commerce.Catalog;
using Mediachase.Commerce.Catalog.Dto;
using Mediachase.Commerce.Pricing;
using Mediachase.Commerce.Storage;
using Mediachase.Data.Provider;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace Nemetos.Galenicare.Framework.Helpers
{
/// <summary>
/// Represents database service to manipulate Entry prices.
///
/// </summary>
[ServiceConfiguration(typeof(IPriceService))]
public class PriceServiceDatabase : IPriceService
{

private static Func<string> _defaultConnectionStringFunction = (Func<string>)(() => CatalogConfiguration.Instance.Connection.AppDatabase);
private static Func<string> _connectionStringFunction = (Func<string>)null;
private const string _catalogKeyPricesCachePrefix = "EP:CatalogKeyPrices";
private const string _masterCacheKey = "EP:CatalogKeyPricesMasterCacheKey";
private readonly ISynchronizedObjectInstanceCache _objectInstanceCache;
public Injected<IPriceDetailService> priceDetailService;
private ICatalogSystem _catalogSystem;
private IChangeNotificationManager _changeManager;

public static Func<string> ConnectionStringFunction
{
get
{
return PriceServiceDatabase._connectionStringFunction ?? PriceServiceDatabase._defaultConnectionStringFunction;
}
set
{
PriceServiceDatabase._connectionStringFunction = value;
}
}

public bool IsReadOnly
{
get
{
return false;
}
}

///// <summary>
///// Creates a new instance of the <see cref="T:Mediachase.Commerce.Pricing.Database.PriceServiceDatabase"/> class.
/////
///// </summary>
///// <param name="catalogSystem">The catalog system, which will be used to get entry.</param><param name="changeManager">The notification manager to notify when changes have been committed.</param>
//public PriceServiceDatabase(ICatalogSystem catalogSystem, IChangeNotificationManager changeManager) : base(catalogSystem, changeManager, ServiceLocator.Current.GetInstance<ISynchronizedObjectInstanceCache>())
//{
//}

/// <summary>
/// Creates a new instance of the <see cref="T:Mediachase.Commerce.Pricing.Database.PriceServiceDatabase"/> class.
///
/// </summary>
/// <param name="catalogSystem">The catalog system, which will be used to get entry.</param><param name="changeManager">The notification manager to notify when changes have been committed.</param><param name="objectInstanceCache">The object instance cache.</param>
public PriceServiceDatabase(ICatalogSystem catalogSystem, IChangeNotificationManager changeManager, ISynchronizedObjectInstanceCache objectInstanceCache)
{
this._catalogSystem = catalogSystem;
this._changeManager = changeManager;
this._objectInstanceCache = objectInstanceCache;
}

public IPriceValue GetDefaultPrice(MarketId market, DateTime validOn, CatalogKey catalogKey, Mediachase.Commerce.Currency currency)
{
PriceFilter filter = new PriceFilter()
{
Quantity = new Decimal?(new Decimal(0)),
Currencies = (IEnumerable<Mediachase.Commerce.Currency>)new Mediachase.Commerce.Currency[1]
{
currency
},
CustomerPricing = (IEnumerable<CustomerPricing>)new CustomerPricing[1]
{
CustomerPricing.AllCustomers
}
};
return Enumerable.SingleOrDefault<IPriceValue>(this.GetPrices(market, validOn, catalogKey, filter));
}

public IEnumerable<IPriceValue> GetPrices(MarketId market, DateTime validOn, CatalogKey catalogKey, PriceFilter filter)
{
bool returnQuantities = !filter.Quantity.HasValue;
Decimal quantity = filter.Quantity ?? new Decimal(0);
return this.GetPricesWorker(market, validOn, (IEnumerable<CatalogKeyAndQuantity>)new List<CatalogKeyAndQuantity>(1)
{
new CatalogKeyAndQuantity(catalogKey, quantity)
}, filter, returnQuantities);
}

public IEnumerable<IPriceValue> GetPrices(MarketId market, DateTime validOn, IEnumerable<CatalogKey> catalogKeys, PriceFilter filter)
{
bool returnQuantities = !filter.Quantity.HasValue;
Decimal quantity = filter.Quantity ?? new Decimal(0);
IEnumerable<CatalogKeyAndQuantity> catalogKeysAndQuantities = Enumerable.Select<CatalogKey, CatalogKeyAndQuantity>(catalogKeys, (Func<CatalogKey, CatalogKeyAndQuantity>)(ck => new CatalogKeyAndQuantity(ck, quantity)));
return this.GetPricesWorker(market, validOn, catalogKeysAndQuantities, filter, returnQuantities);
}

public IEnumerable<IPriceValue> GetPrices(MarketId market, DateTime validOn, IEnumerable<CatalogKeyAndQuantity> catalogKeysAndQuantities, PriceFilter filter)
{
return this.GetPricesWorker(market, validOn, catalogKeysAndQuantities, filter, false);
}

public IEnumerable<IPriceValue> GetCatalogEntryPrices(CatalogKey catalogKey)
{
return this.GetCatalogEntryPrices((IEnumerable<CatalogKey>)new CatalogKey[1]
{
catalogKey
});
}

/// <summary>
/// Gets catalog entry prices from the list of catalog key.
///
/// </summary>
/// <param name="catalogKeys">List of catalog key to get prices.</param>
/// <returns>
/// Catalog entry prices.
/// </returns>
public IEnumerable<IPriceValue> GetCatalogEntryPrices(IEnumerable<CatalogKey> catalogKeys)
{
List<IPriceValue> list = new List<IPriceValue>();
List<CatalogKey> itemsToLoad = new List<CatalogKey>();
foreach (CatalogKey catalogKey in catalogKeys)
{
IEnumerable<IPriceValue> loadedPrices;
if (this.TryGetCachedPrices(catalogKey, out loadedPrices))
list.AddRange(loadedPrices);
else
itemsToLoad.Add(catalogKey);
}
if (itemsToLoad.Count > 0)
list.AddRange(this.GetCatalogEntryPricesWorker(itemsToLoad));
return (IEnumerable<IPriceValue>)list;
}

/// <summary>
/// Gets catalog entry prices from database.
///
/// </summary>
/// <param name="itemsToLoad">List of catalog key to get prices.</param>
/// <returns>
/// Catalog entry prices.
/// </returns>
private IEnumerable<IPriceValue> GetCatalogEntryPricesWorker(List<CatalogKey> itemsToLoad)
{
List<IPriceValue> list = new List<IPriceValue>();
using (PriceDto priceDto = new PriceDto())
{
DataCommand command = new DataCommand()
{
ConnectionString = PriceServiceDatabase.ConnectionStringFunction(),
CommandType = CommandType.StoredProcedure,
CommandText = "ecf_Pricing_GetCatalogEntryPrices",
TableMapping = DataHelper.MapTables("Price"),
DataSet = (DataSet)priceDto
};

string market = GetUserMarketEligiblility();
command.Parameters.Add(new DataParameter("CatalogKeys", (object)TableTypeSerializers.CreateCatalogKeysTable((IEnumerable<CatalogKey>)itemsToLoad)));
DataService.LoadDataSet(command);
EnumerableRowCollection<IPriceValue> enumerableRowCollection = TypedTableBaseExtensions.Select<PriceDto.PriceRow, IPriceValue>((TypedTableBase<PriceDto.PriceRow>)priceDto.Price, (Func<PriceDto.PriceRow, IPriceValue>)(pg => this.DeserializePriceValue(pg)));

if (market != string.Empty)
{
if (market != "ALL")
{
enumerableRowCollection = enumerableRowCollection.Where(x => x.MarketId.Value.ToLower() == market.ToLower());

}
list.AddRange((IEnumerable<IPriceValue>)enumerableRowCollection);
this.InsertPriceValuesCache((IEnumerable<IPriceValue>)enumerableRowCollection);
}

}
return (IEnumerable<IPriceValue>)list;
}
public string GetUserMarketEligiblility()
{
string market = string.Empty;
bool isSunstore = System.Web.Security.Roles.IsUserInRole("SunstoreGrp");
bool isAmavita = System.Web.Security.Roles.IsUserInRole("AmavitaGrp");
bool isCoop = System.Web.Security.Roles.IsUserInRole("CoopGrp");
bool isAdmin = System.Web.Security.Roles.IsUserInRole("Administrators");
if (!isAdmin)
{
if (isSunstore)
{
market = "DEFAULT";
}
if (isAmavita)
{
market = "Amavita";
}
if (isCoop)
{
market = "Coop";
}
if (string.IsNullOrEmpty(market))
{
market = "DEFAULT";
}
}
else
{
market = "ALL";
}

return market;
}

/// <summary>
/// Inserts price value to cache, for each specific CatalogKey.
///
/// </summary>
/// <param name="prices">Prices to insert to cache.</param>
private void InsertPriceValuesCache(IEnumerable<IPriceValue> prices)
{
if (prices == null)
return;
foreach (IGrouping<CatalogKey, IPriceValue> grouping in Enumerable.GroupBy<IPriceValue, CatalogKey>(prices, (Func<IPriceValue, CatalogKey>)(c => c.CatalogKey)))
{
string keyPriceCacheKey = this.GetCatalogKeyPriceCacheKey(grouping.Key);
if (!string.IsNullOrEmpty(keyPriceCacheKey))
{
IEnumerable<IPriceValue> enumerable = Enumerable.Select<IPriceValue, IPriceValue>((IEnumerable<IPriceValue>)grouping, (Func<IPriceValue, IPriceValue>)(g => g));
this._objectInstanceCache.Insert(keyPriceCacheKey, (object)enumerable, new CacheEvictionPolicy((IEnumerable<string>)null, (IEnumerable<string>)null, (IEnumerable<string>)new string[1]
{
"EP:CatalogKeyPricesMasterCacheKey"
}, new TimeSpan(0, 0, 1), CacheTimeoutType.Absolute));
}
}
}

/// <summary>
/// Gets CatalogKey's price cache key.
///
/// </summary>
/// <param name="catalogKey">CatalogKey object to get its price cache key.</param>
/// <returns>
/// A string represents CatalogKey's price cache key, or null if the CatalogKey is null.
/// </returns>
private string GetCatalogKeyPriceCacheKey(CatalogKey catalogKey)
{
if (catalogKey == null)
return (string)null;
return string.Format("{0}:{1}:{2}", (object)"EP:CatalogKeyPrices", (object)catalogKey.ApplicationId, (object)catalogKey.CatalogEntryCode);
}

public void SetCatalogEntryPrices(CatalogKey catalogKey, IEnumerable<IPriceValue> priceValues)
{
this.SetCatalogEntryPrices((IEnumerable<CatalogKey>)new CatalogKey[1]
{
catalogKey
}, priceValues);
}

public void SetCatalogEntryPrices(IEnumerable<CatalogKey> catalogKeys, IEnumerable<IPriceValue> priceValues)
{
this.SetCatalogEntryPricesWorker(catalogKeys, priceValues, true);
}

public void ReplicatePriceDetailChanges(IEnumerable<CatalogKey> catalogKeys, IEnumerable<IPriceValue> priceValues)
{
this.SetCatalogEntryPricesWorker(catalogKeys, priceValues, false);
}

private void SetCatalogEntryPricesWorker(IEnumerable<CatalogKey> catalogKeys, IEnumerable<IPriceValue> priceValues, bool copyToPriceDetailService)
{
HashSet<CatalogKey> hashSet = new HashSet<CatalogKey>(catalogKeys);
List<IPriceValue> list = new List<IPriceValue>();
using (TransactionScope transactionScope = new TransactionScope())
{
foreach (IPriceValue priceValue in priceValues)
{
if (!hashSet.Contains(priceValue.CatalogKey))
throw new InvalidOperationException("All price values must have a corresponding catalog key value.");
list.Add(priceValue);
}
List<IBulkChangeItem<CatalogEntryChange>> changes = Enumerable.ToList<IBulkChangeItem<CatalogEntryChange>>(Enumerable.Select<CatalogEntryChange, IBulkChangeItem<CatalogEntryChange>>(this.GetCatalogEntryChanges((IEnumerable<CatalogKey>)hashSet), (Func<CatalogEntryChange, IBulkChangeItem<CatalogEntryChange>>)(c => BulkChangeItem.ForChange<CatalogEntryChange>(c, c))));
DataCommand command = new DataCommand()
{
ConnectionString = PriceServiceDatabase.ConnectionStringFunction(),
CommandType = CommandType.StoredProcedure,
CommandText = "ecf_Pricing_SetCatalogEntryPrices"
};
command.Parameters.Add(new DataParameter("CatalogKeys", (object)TableTypeSerializers.CreateCatalogKeysTable((IEnumerable<CatalogKey>)hashSet)));
command.Parameters.Add(new DataParameter("PriceValues", (object)TableTypeSerializers.CreatePriceValuesTable((IEnumerable<IPriceValue>)list)));
DataService.ExecuteNonExec(command);
TransactionScope.OnCommit((Action)(() => this._changeManager.NotifyChanges<CatalogEntryChange>((IEnumerable<IBulkChangeItem<CatalogEntryChange>>)changes)));
if (copyToPriceDetailService)
this.priceDetailService.Service.ReplicatePriceServiceChanges((IEnumerable<CatalogKey>)hashSet, (IEnumerable<IPriceValue>)list);
CatalogCache.Clear();
foreach (CatalogKey catalogKey in catalogKeys)
this._objectInstanceCache.Remove(this.GetCatalogKeyPriceCacheKey(catalogKey));
transactionScope.Complete();
}
}

internal void EnsurePriceTypes(IDictionary<CustomerPricing.PriceType, PriceTypeDefinition> priceTypes)
{
using (TransactionScope transactionScope = new TransactionScope())
{
DataCommand command = new DataCommand()
{
ConnectionString = PriceServiceDatabase.ConnectionStringFunction(),
CommandType = CommandType.StoredProcedure,
CommandText = "ecf_Pricing_EnsurePriceTypes"
};
command.Parameters.Add(new DataParameter("PriceTypes", (object)TableTypeSerializers.CreatePriceTypesTable(priceTypes)));
DataService.ExecuteNonExec(command);
transactionScope.Complete();
}
}

private IEnumerable<IPriceValue> GetPricesWorker(MarketId market, DateTime validOn, IEnumerable<CatalogKeyAndQuantity> catalogKeysAndQuantities, PriceFilter filter, bool returnQuantities)
{
List<IPriceValue> list = new List<IPriceValue>();
List<CatalogKey> itemsToLoad = new List<CatalogKey>();
foreach (CatalogKeyAndQuantity catalogKeyAndQuantity in catalogKeysAndQuantities)
{
IEnumerable<IPriceValue> loadedPrices;
if (this.TryGetCachedPrices(catalogKeyAndQuantity.CatalogKey, out loadedPrices))
list.AddRange(this.FilterPrices(loadedPrices, market, validOn, catalogKeyAndQuantity.Quantity, filter, returnQuantities));
else
itemsToLoad.Add(catalogKeyAndQuantity.CatalogKey);
}
if (itemsToLoad.Count > 0)
{
foreach (IGrouping<CatalogKey, IPriceValue> grouping in Enumerable.GroupBy<IPriceValue, CatalogKey>(this.GetCatalogEntryPricesWorker(itemsToLoad), (Func<IPriceValue, CatalogKey>)(c => c.CatalogKey)))
{
IGrouping<CatalogKey, IPriceValue> group = grouping;
CatalogKeyAndQuantity catalogKeyAndQuantity = Enumerable.FirstOrDefault<CatalogKeyAndQuantity>(catalogKeysAndQuantities, (Func<CatalogKeyAndQuantity, bool>)(c => group.Key.Equals(c.CatalogKey)));
if (catalogKeyAndQuantity != null)
{
IEnumerable<IPriceValue> source = Enumerable.Select<IPriceValue, IPriceValue>((IEnumerable<IPriceValue>)group, (Func<IPriceValue, IPriceValue>)(g => g));
list.AddRange(this.FilterPrices(source, market, validOn, catalogKeyAndQuantity.Quantity, filter, returnQuantities));
}
}
}
return (IEnumerable<IPriceValue>)list;
}

/// <summary>
/// Tries get cached prices of CatalogKey.
///
/// </summary>
/// <param name="catalogKey">CatalogKey to get cached prices.</param><param name="loadedPrices">Reference to the prices list of <paramref name="catalogKey"/>, which will be loaded if cached prices are found.</param>
/// <returns>
/// True if cached prices are found, and <paramref name="catalogKey"/> will be loaded with cached prices. Otherwise, returns false, and <paramref name="catalogKey"/> will remain unchanged.
/// </returns>
private bool TryGetCachedPrices(CatalogKey catalogKey, out IEnumerable<IPriceValue> loadedPrices)
{
string keyPriceCacheKey = this.GetCatalogKeyPriceCacheKey(catalogKey);
if (string.IsNullOrEmpty(keyPriceCacheKey))
{
loadedPrices = (IEnumerable<IPriceValue>)null;
return false;
}
loadedPrices = this._objectInstanceCache.Get(keyPriceCacheKey) as IEnumerable<IPriceValue>;
return loadedPrices != null;
}

private IEnumerable<IPriceValue> FilterPrices(IEnumerable<IPriceValue> source, MarketId market, DateTime validOn, Decimal quantity, PriceFilter filter, bool returnQuantities)
{
IEnumerable<IPriceValue> enumerable = this.FilterPricesAllQuantities(source, market, validOn, quantity, filter, returnQuantities);
if (!returnQuantities)
{
List<IPriceValue> list = new List<IPriceValue>();
foreach (IPriceValue priceValue in enumerable)
{
IPriceValue incomingPrice = priceValue;
int index = list.FindIndex((Predicate<IPriceValue>)(p =>
{
if (p.UnitPrice.Currency == incomingPrice.UnitPrice.Currency)
return p.CustomerPricing.Equals(incomingPrice.CustomerPricing);
return false;
}));
if (index == -1)
list.Add(incomingPrice);
else if (list[index].MinQuantity < incomingPrice.MinQuantity)
list[index] = incomingPrice;
}
enumerable = (IEnumerable<IPriceValue>)list;
}
return enumerable;
}

private IEnumerable<IPriceValue> FilterPricesAllQuantities(IEnumerable<IPriceValue> source, MarketId market, DateTime validOn, Decimal quantity, PriceFilter filter, bool returnQuantities)
{
string userMarket = GetUserMarketEligiblility();

if (userMarket == "ALL")
{
return Enumerable.Where<IPriceValue>(source, (Func<IPriceValue, bool>)(p =>
{
if (!(p.ValidFrom <= validOn) || p.ValidUntil.HasValue && !(validOn < p.ValidUntil.Value) || (!returnQuantities && !(p.MinQuantity <= quantity) || Enumerable.Count<Mediachase.Commerce.Currency>(filter.Currencies) != 0 && !Enumerable.Contains<Mediachase.Commerce.Currency>(filter.Currencies, p.UnitPrice.Currency)))
return false;
if (Enumerable.Count<CustomerPricing>(filter.CustomerPricing) != 0)
return Enumerable.Any<CustomerPricing>(filter.CustomerPricing, (Func<CustomerPricing, bool>)(cp => cp.Equals(p.CustomerPricing)));
return true;
}));
}

return Enumerable.Where<IPriceValue>(source, (Func<IPriceValue, bool>)(p =>
{
if (!(p.MarketId.Value == market) || !(p.ValidFrom <= validOn) || p.ValidUntil.HasValue && !(validOn < p.ValidUntil.Value) || (!returnQuantities && !(p.MinQuantity <= quantity) || Enumerable.Count<Mediachase.Commerce.Currency>(filter.Currencies) != 0 && !Enumerable.Contains<Mediachase.Commerce.Currency>(filter.Currencies, p.UnitPrice.Currency)))
return false;
if (Enumerable.Count<CustomerPricing>(filter.CustomerPricing) != 0)
return Enumerable.Any<CustomerPricing>(filter.CustomerPricing, (Func<CustomerPricing, bool>)(cp => cp.Equals(p.CustomerPricing)));
return true;
}));

}

private IPriceValue DeserializePriceValue(PriceDto.PriceRow priceRow)
{
return (IPriceValue)new ReadOnlyPriceValue(new CatalogKey(priceRow.ApplicationId, priceRow.CatalogEntryCode), new MarketId(priceRow.MarketId), priceRow.IsPriceCodeNull() ? (CustomerPricing)null : new CustomerPricing((CustomerPricing.PriceType)priceRow.PriceTypeId, priceRow.PriceCode), priceRow.ValidFrom, priceRow.IsValidUntilNull() ? new DateTime?() : new DateTime?(priceRow.ValidUntil), priceRow.MinQuantity, new Money(priceRow.UnitPrice, (Mediachase.Commerce.Currency)priceRow.CurrencyCode));
}

private IEnumerable<CatalogEntryChange> GetCatalogEntryChanges(IEnumerable<CatalogKey> catalogKeys)
{
foreach (IGrouping<CatalogKey, CatalogKey> grouping in Enumerable.GroupBy<CatalogKey, CatalogKey>(catalogKeys, (Func<CatalogKey, CatalogKey>)(k => k)))
{
CatalogKey keyWithKnownId = Enumerable.FirstOrDefault<CatalogKey>((IEnumerable<CatalogKey>)grouping, (Func<CatalogKey, bool>)(k => (k.CreatedForRow != null ? new int?(k.CreatedForRow.CatalogEntryId) : (k.CreatedForEntry != null ? new int?(k.CreatedForEntry.CatalogEntryId) : new int?())).HasValue));
if (keyWithKnownId == null)
{
CatalogKey key = Enumerable.First<CatalogKey>((IEnumerable<CatalogKey>)grouping);
CatalogEntryDto catalogEntryDto = this._catalogSystem.GetCatalogEntryDto(key.CatalogEntryCode);
yield return new CatalogEntryChange(Enumerable.Single<CatalogEntryDto.CatalogEntryRow>((IEnumerable<CatalogEntryDto.CatalogEntryRow>)catalogEntryDto.CatalogEntry).CatalogEntryId, key.ApplicationId, key.CatalogEntryCode);
}
else
yield return new CatalogEntryChange((keyWithKnownId.CreatedForRow != null ? new int?(keyWithKnownId.CreatedForRow.CatalogEntryId) : (keyWithKnownId.CreatedForEntry != null ? new int?(keyWithKnownId.CreatedForEntry.CatalogEntryId) : new int?())).Value, keyWithKnownId.ApplicationId, keyWithKnownId.CatalogEntryCode);
}
}
}
}

All i modified is to take into consideration the logged user when filtering the prices.

thank you in advance, Andrei

Can you take a look at PriceGroup/PriceValue tables before and after updating in CM? Are there anything changed?

/Q

hello quan, i checked for the same variant on both price value and price group before and after updateing in CM:

BEFORE:

AFTER:

besides the modified time and validity i dont see anything changed) the unit price for the first is changed cause i made a modification in order to save). Also the validity is ok cause the years differ although i see they dont appear in the table. 

Ok, so data is unchanged. Does GetUserMarketEligiblility return expected market after you edit the entry in CM? It looks suspicious

/Q

It returns the name of the market based on what kind of user he is. But that doesnt change anything.If i debug, the GetPricesWorker returns the correct information with the correct prices based on the users