private static NullResponse DeleteCart(DeleteCartDataRequest request)
{
ThrowIf.Null(request, "request");
int errorCode;
using (StringIdTableType transactionIdsTableType = new StringIdTableType(request.SalesTransactionIds, string.Empty))
{
ParameterSet parameters = new ParameterSet();
parameters[SalesTransactionIdsTableTypeVariableName] = transactionIdsTableType;
using (SqlServerDatabaseContext sqlServerDatabaseContext = new SqlServerDatabaseContext(request))
{
errorCode = sqlServerDatabaseContext.ExecuteStoredProcedureNonQuery(DeleteSalesTransactionsSprocName, parameters);
}
}
if (errorCode != (int)DatabaseErrorCodes.Success)
{
if (errorCode == (int)DatabaseErrorCodes.AuthorizationError)
{
throw new SecurityException(SecurityErrors.Microsoft_Dynamics_Commerce_Runtime_AuthorizationFailed, "One or more transactions could not be deleted.");
}
throw new StorageException(StorageErrors.Microsoft_Dynamics_Commerce_Runtime_CriticalStorageError, errorCode, "Unable to delete transactions.");
}
return(new NullResponse());
}
private EntityDataServiceResponse <OrgUnitAddress> GetOrgUnitAddress(GetOrgUnitAddressDataRequest request)
{
ThrowIf.Null(request, "request");
ThrowIf.Null(request.ChannelIds, "request.ChannelIds");
ThrowIf.Null(request.QueryResultSettings, "request.QueryResultSettings");
var query = new SqlPagedQuery(request.QueryResultSettings)
{
From = OrgUnitAddressView,
OrderBy = "CHANNELID"
};
PagedResult <OrgUnitAddress> results;
IEnumerable <string> distinctChannelIds = request.ChannelIds.Distinct <long>().Select <long, string>(id => id.ToString());
using (StringIdTableType channelIdsTable = new StringIdTableType(distinctChannelIds, "CHANNELID"))
{
query.Parameters["@TVP_CHANNELID"] = channelIdsTable;
using (var sqlServerDatabaseContext = new SqlServerDatabaseContext(request))
{
results = sqlServerDatabaseContext.ReadEntity <OrgUnitAddress>(query);
}
}
return(new EntityDataServiceResponse <OrgUnitAddress>(results));
}
/// <summary>
/// Gets the reason sub codes with given reason code identifier or reason sub code identifier.
/// </summary>
/// <param name="reasonCodeIds">The reason code identifier.</param>
/// <param name="reasonSubCodeId">The reason sub code identifier.</param>
/// <param name="settings">The query result settings.</param>
/// <param name="request">The get reason codes data request.</param>
/// <returns>The info sub codes.</returns>
private static PagedResult <ReasonSubCode> GetReasonSubCodes(IEnumerable <string> reasonCodeIds, string reasonSubCodeId, QueryResultSettings settings, GetReasonCodesDataRequest request)
{
ThrowIf.Null(reasonCodeIds, "reasonCodeIds");
ThrowIf.Null(settings, "settings");
var query = new SqlPagedQuery(settings)
{
From = ReasonSubCodeFunctionName,
Aliased = true
};
BuildSubReasonCodesQuery(reasonCodeIds, reasonSubCodeId, query, request.RequestContext);
PagedResult <ReasonSubCode> reasonSubcodes;
using (StringIdTableType type = new StringIdTableType(reasonCodeIds, "REASONCODEID"))
{
query.Parameters["@TVP_INFOCODEIDTABLETYPE"] = type;
using (SqlServerDatabaseContext sqlServerDatabaseContext = new SqlServerDatabaseContext(request))
{
reasonSubcodes = sqlServerDatabaseContext.ReadEntity <ReasonSubCode>(query);
}
return(reasonSubcodes);
}
}
private static EntityDataServiceResponse <ProductVariant> GetVariantsByDimensionIds(GetVariantsByDimensionIdsDataRequest request)
{
IEnumerable <string> inventoryDimensionIds = request.InventoryDimensionIds;
RequestContext context = request.RequestContext;
ThrowIf.Null(inventoryDimensionIds, "inventoryDimensionIds");
ThrowIf.Null(context, "context");
ItemL2CacheDataStoreAccessor level2CacheDataAccessor = GetCacheAccessor(context);
bool found;
bool updateL2Cache;
ReadOnlyCollection <ProductVariant> result = DataManager.GetDataFromCache(() => level2CacheDataAccessor.GetVariantsByDimensionIds(inventoryDimensionIds), out found, out updateL2Cache);
if (!found)
{
if (inventoryDimensionIds.Any())
{
var settings = new QueryResultSettings(PagingInfo.CreateWithExactCount(inventoryDimensionIds.Count(), 0));
var query = new SqlPagedQuery(settings)
{
Select = new ColumnSet(),
From = InventDimViewName,
Where = string.Format("{0} = {1}", DataAreaIdColumnName, DataAreaIdVariableName)
};
query.Parameters[DataAreaIdVariableName] = context.GetChannelConfiguration().InventLocationDataAreaId;
using (StringIdTableType type = new StringIdTableType(inventoryDimensionIds, InventDimIdColumnName))
{
query.Parameters[ItemIdTableTypeVariableName] = type;
using (DatabaseContext databaseContext = new DatabaseContext(context))
{
result = databaseContext.ReadEntity <ProductVariant>(query).Results;
}
}
}
else
{
result = new ReadOnlyCollection <ProductVariant>(new ProductVariant[0]);
}
updateL2Cache &= result != null &&
result.Count < MaxCachedCollectionSize;
}
if (updateL2Cache)
{
level2CacheDataAccessor.PutVariantDimensionsByItemIds(inventoryDimensionIds, result);
}
return(new EntityDataServiceResponse <ProductVariant>(result.AsPagedResult()));
}
/// <summary>
/// Gets a button grids by identifiers.
/// </summary>
/// <param name="request">The get button grids data request.</param>
/// <returns>
/// Collection of matching button grids.
/// </returns>
private EntityDataServiceResponse <ButtonGrid> GetButtonsGrids(GetButtonGridsDataRequest request)
{
ThrowIf.Null(request, "request");
ThrowIf.Null(request.ButtonGridIds, "request.ButtonGridIds");
ThrowIf.Null(request.QueryResultSettings, "request.QueryResultSettings");
PagedResult <ButtonGrid> buttonGrids = null;
// Default query to retrieve all the button grids.
var query = new SqlPagedQuery(request.QueryResultSettings)
{
From = ButtonGridsView,
OrderBy = ButtonGrid.ButtonGridIdColumn
};
// Update query when only one button grid is retrieved.
if (request.ButtonGridIds.Count() == 1)
{
query.Where = string.Format("{0} = @{0}", ButtonGrid.ButtonGridIdColumn);
query.Parameters[string.Format("@{0}", ButtonGrid.ButtonGridIdColumn)] = request.ButtonGridIds.FirstOrDefault();
}
// Update query for retrieving multiple button grids.
if (request.ButtonGridIds.HasMultiple())
{
using (StringIdTableType buttonGridIdTableType = new StringIdTableType(request.ButtonGridIds, ButtonGrid.ButtonGridIdColumn))
{
query.Parameters[ButtonGridIdTableTypeParameterName] = buttonGridIdTableType;
// Query execution for retrieving multiple button grids.
buttonGrids = this.ExecuteQuery <ButtonGrid>(query, request.RequestContext);
}
}
else
{
// Query execution for retrieving one or all the button grids.
buttonGrids = this.ExecuteQuery <ButtonGrid>(query, request.RequestContext);
}
// Get the button grid buttons.
if (buttonGrids != null && buttonGrids.Results != null)
{
var buttonGridIds = buttonGrids.Results.Select(b => b.Id);
ReadOnlyCollection <ButtonGridButton> buttons = this.GetButtonGridButtons(buttonGridIds, request.RequestContext).Results;
foreach (var buttonGrid in buttonGrids.Results)
{
buttonGrid.Buttons = buttons.Where(b => string.Equals(b.ButtonGridId, buttonGrid.Id, StringComparison.OrdinalIgnoreCase));
}
}
return(new EntityDataServiceResponse <ButtonGrid>(buttonGrids));
}
public EntityDataServiceResponse <LoyaltyRewardPointLine> Execute()
{
using (StringIdTableType transactionIdTableType = new StringIdTableType(this.request.Criteria.TransactionIds, RetailTransactionTableSchema.TransactionIdColumn))
{
var query = new SqlPagedQuery(this.request.QueryResultSettings)
{
From = RetailTransactionLoyaltyRewardPointTransView,
};
query.Parameters["@TVP_TRANSACTIONIDTABLETYPE"] = transactionIdTableType;
PagedResult <LoyaltyRewardPointLine> results = this.databaseContext.ReadEntity <LoyaltyRewardPointLine>(query);
return(new EntityDataServiceResponse <LoyaltyRewardPointLine>(results));
}
}
private EntityDataServiceResponse <TaxLine> GetTaxLines(GetTaxLinesDataRequest request)
{
using (DatabaseContext databaseContext = new DatabaseContext(request.RequestContext))
using (StringIdTableType transactionIdsTableType = new StringIdTableType(request.TransactionIds, TaxLine.TransactionIdColumn))
{
var query = new SqlPagedQuery(QueryResultSettings.AllRecords)
{
From = RetailTransactionTaxTransView
};
query.Parameters["@TVP_TABLETYPE"] = transactionIdsTableType;
PagedResult <TaxLine> results = databaseContext.ReadEntity <TaxLine>(query);
return(new EntityDataServiceResponse <TaxLine>(results));
}
}
/// <summary>
/// Retrieves the units of measure for the given unit identifiers.
/// If no unit identifiers are provided then all the supported units of measure are retrieved.
/// </summary>
/// <param name="request">The units of measure request.</param>
/// <returns>
/// A unit of measure for the symbol.
/// </returns>
private EntityDataServiceResponse <UnitOfMeasure> GetUnitsOfMeasure(GetUnitsOfMeasureDataRequest request)
{
ThrowIf.Null(request, "request");
ThrowIf.Null(request.UnitIds, "request.UnitIds");
ThrowIf.Null(request.QueryResultSettings, "request.QueryResultSettings");
PagedResult <UnitOfMeasure> results;
// Default query to retrieve all units of measure.
var query = new SqlPagedQuery(request.QueryResultSettings)
{
From = GetUnitsOfMeasureFunctionName,
Aliased = true,
OrderBy = SymbolColumnName,
};
query.Parameters["@LanguageId"] = request.RequestContext.LanguageId;
// Update query when only one unit of measure is retrieved.
if (request.UnitIds.Count() == 1)
{
query.Where = string.Format("{0} = @UnitId", SymbolColumnName);
query.Parameters["@UnitId"] = request.UnitIds.FirstOrDefault();
}
// Update query when multiple units of measure are retrieved.
if (request.UnitIds.HasMultiple())
{
IEnumerable <string> distinctUnitOfMeasure = request.UnitIds.Distinct(StringComparer.OrdinalIgnoreCase);
using (StringIdTableType type = new StringIdTableType(distinctUnitOfMeasure, SymbolColumnName))
{
query.Parameters["@TVP_UNITIDTABLETYPE"] = type;
// Query execution for retrieving multiple units.
results = this.ExecuteQuery(query, request.RequestContext);
}
}
else
{
// Query execution for retrieving single or all the units.
results = this.ExecuteQuery(query, request.RequestContext);
}
return(new EntityDataServiceResponse <UnitOfMeasure>(results));
}
/// <summary>
/// Executes the procedure.
/// </summary>
/// <returns>The collection of sub reason codes.</returns>
public ReadOnlyCollection <ReasonSubCode> Execute()
{
const string GetSubReasonCodesQueryString = @"
(
SELECT
iscv.[REASONCODEID] AS REASONCODEID,
iscv.[SUBCODEID] AS SUBCODEID,
iscv.[RECID] AS RECID,
iscv.[TRIGGERFUNCTION] AS TRIGGERFUNCTION,
iscv.[TRIGGERCODE] AS TRIGGERCODE,
iscv.[NEWSALESLINE] AS NEWSALESLINE,
iscv.[PRICETYPE] AS PRICETYPE,
iscv.[AMOUNTPERCENT] AS AMOUNTPERCENT,
COALESCE(risct.[DESCRIPTION], risctd.[DESCRIPTION], iscv.[SUBCODEID]) AS DESCRIPTION,
COALESCE(risct.[LANGUAGEID], risctd.[LANGUAGEID]) AS LANGUAGEID
FROM [crt].[INFOSUBCODEVIEW] iscv
LEFT JOIN [ax].[RETAILINFORMATIONSUBCODETRANSLATION] risct
ON iscv.[RECID] = risct.[INFOSUBCODE]
AND risct.[LANGUAGEID] = @languageId
AND risct.[DATAAREAID] = @DataAreaId
LEFT JOIN [ax].[RETAILINFORMATIONSUBCODETRANSLATION] risctd
ON iscv.[RECID] = risctd.[INFOSUBCODE]
AND risctd.[LANGUAGEID] = @defaultlanguageId
AND risctd.[DATAAREAID] = @DataAreaId
WHERE iscv.[DATAAREAID] = @DataAreaId
)";
SqlPagedQuery query = new SqlPagedQuery(this.settings)
{
From = GetSubReasonCodesQueryString,
Aliased = true,
DatabaseSchema = string.Empty
};
this.BuildSubReasonCodesQuery(this.reasonCodeIds, this.reasonSubCodeId, query);
ReadOnlyCollection <ReasonSubCode> reasonSubCodes;
using (StringIdTableType type = new StringIdTableType(this.reasonCodeIds, "REASONCODEID"))
{
query.Parameters["@TVP_INFOCODEIDTABLETYPE"] = type;
reasonSubCodes = this.databaseContext.ReadEntity <ReasonSubCode>(query).Results;
}
return(reasonSubCodes);
}
/// <summary>
/// Gets the items using the specified item identifiers.
/// </summary>
/// <param name="context">The request context.</param>
/// <param name="itemIds">The list of item identifiers.</param>
/// <param name="settings">The query result settings.</param>
/// <returns>The collection of items.</returns>
private static ReadOnlyCollection <Item> GetItems(RequestContext context, IEnumerable <string> itemIds, QueryResultSettings settings)
{
ThrowIf.Null(context, "context");
ThrowIf.Null(itemIds, "itemIds");
ThrowIf.Null(settings, "settings");
ItemL2CacheDataStoreAccessor level2CacheDataAccessor = GetCacheAccessor(context);
bool found;
bool updateL2Cache;
ReadOnlyCollection <Item> result = DataManager.GetDataFromCache(() => level2CacheDataAccessor.GetItems(itemIds, settings), out found, out updateL2Cache);
if (!found)
{
using (StringIdTableType type = new StringIdTableType(itemIds, "ITEMID"))
{
var parameters = new ParameterSet();
parameters[DatabaseAccessor.ChannelIdVariableName] = context.GetPrincipal().ChannelId;
parameters[DatabaseAccessor.ChannelDateVariableName] = context.GetNowInChannelTimeZone().DateTime;
parameters[ItemIdsVariableName] = type.DataTable;
using (SqlServerDatabaseContext databaseContext = new SqlServerDatabaseContext(context, settings))
{
result = databaseContext.ExecuteStoredProcedure <Item>(GetItemsByItemIdsSprocName, parameters).Results;
}
}
updateL2Cache &= result != null &&
result.Count < MaxCachedCollectionSize;
}
if (updateL2Cache)
{
level2CacheDataAccessor.PutItems(itemIds, settings, result);
}
return(result);
}
/// <summary>
/// Get buttons for a button grid.
/// </summary>
/// <param name="buttonGridIds">The button grid identifiers.</param>
/// <param name="context">The request context.</param>
/// <returns>Collection of button grid buttons.</returns>
private PagedResult <ButtonGridButton> GetButtonGridButtons(IEnumerable <string> buttonGridIds, RequestContext context)
{
if (buttonGridIds == null || !buttonGridIds.Any())
{
return((new List <ButtonGridButton>()).AsPagedResult());
}
var query = new SqlPagedQuery(QueryResultSettings.AllRecords)
{
From = ButtonGridButtonsView,
OrderBy = ButtonGridButtonIdColumn
};
PagedResult <ButtonGridButton> buttonGridButtons;
using (StringIdTableType buttonGridIdTableType = new StringIdTableType(buttonGridIds, ButtonGrid.ButtonGridIdColumn))
{
query.Parameters[ButtonGridIdTableTypeParameterName] = buttonGridIdTableType;
buttonGridButtons = this.ExecuteQuery <ButtonGridButton>(query, context);
}
return(buttonGridButtons ?? new List <ButtonGridButton>().AsPagedResult());
}
private static EntityDataServiceResponse <ReasonCode> GetReasonCodes(GetReasonCodesDataRequest request)
{
ThrowIf.Null(request, "request");
ThrowIf.Null(request.QueryResultSettings, "request.QueryResultSettings");
string channelLanguageId = GetDefaultLanguageId(request.RequestContext);
string employeeLanguageId = GetEmployeeLanguageId(request.RequestContext);
ReasonCodeL2CacheDataStoreAccessor level2CacheDataAccessor = GetReasonCodeL2CacheDataStoreAccessor(request.RequestContext);
bool found;
bool updateL2Cache;
PagedResult <ReasonCode> result = DataManager.GetDataFromCache(() => level2CacheDataAccessor.GetReasonCodes(request.ReasonCodeIds, channelLanguageId, employeeLanguageId, request.QueryResultSettings), out found, out updateL2Cache);
if (!found)
{
SortingInfo sortingInfo = request.QueryResultSettings.Sorting;
if (!sortingInfo.IsSpecified)
{
sortingInfo = new SortingInfo(ReasonCode.PriorityColumn, false);
}
var query = new SqlPagedQuery(request.QueryResultSettings)
{
OrderBy = sortingInfo.ToString(),
From = ReasonCodeFunctionName,
Aliased = true,
};
AddLanguageIdToQuery(query, request.RequestContext);
AddDataAreaIdToQuery(query, request.RequestContext);
// Load info codes
using (SqlServerDatabaseContext sqlServerDatabaseContext = new SqlServerDatabaseContext(request))
using (StringIdTableType groupIds = new StringIdTableType(request.ReasonCodeIds, "GROUPID"))
{
// the view sets the INFOCODEID to GROUPID when the reason code is not part of a group, so we always query by GROUPID
query.Parameters["@tvp_groupIds"] = groupIds.DataTable;
result = sqlServerDatabaseContext.ReadEntity <ReasonCode>(query);
}
// Load sub codes
if (result.Results.Any())
{
QueryResultSettings subCodeSettings = QueryResultSettings.AllRecords;
var subcodes = GetReasonSubCodes(result.Results.Select(x => x.ReasonCodeId), null, subCodeSettings, request).Results.ToLookup(x => x.ReasonCodeId);
foreach (var infoCode in result.Results)
{
infoCode.ReasonSubCodes.Clear();
infoCode.ReasonSubCodes.AddRange(subcodes[infoCode.ReasonCodeId]);
}
}
updateL2Cache &= result != null && result.Results.Count < MaxCachedCollectionSize;
}
if (updateL2Cache)
{
level2CacheDataAccessor.PutReasonCodes(request.ReasonCodeIds, channelLanguageId, employeeLanguageId, request.QueryResultSettings, result);
}
return(new EntityDataServiceResponse <ReasonCode>(result));
}
/// <summary>
/// Executes the procedure.
/// </summary>
/// <returns>The collection of reason codes.</returns>
public PagedResult <ReasonCode> Execute()
{
const string GetReasonCodesQueryString = @"
(
SELECT
icv.[REASONCODEID] AS REASONCODEID,
icv.[RECID] AS RECID,
icv.[ONCEPERTRANSACTION] AS ONCEPERTRANSACTION,
icv.[PRINTPROMPTONRECEIPT] AS PRINTPROMPTONRECEIPT,
icv.[PRINTINPUTONRECEIPT] AS PRINTINPUTONRECEIPT,
icv.[PRINTINPUTNAMEONRECEIPT] AS PRINTINPUTNAMEONRECEIPT,
icv.[INPUTTYPE] AS INPUTTYPE,
icv.[MINIMUMVALUE] AS MINIMUMVALUE,
icv.[MAXIMUMVALUE] AS MAXIMUMVALUE,
icv.[MINIMUMLENGTH] AS MINIMUMLENGTH,
icv.[MAXIMUMLENGTH] AS MAXIMUMLENGTH,
icv.[INPUTREQUIRED] AS INPUTREQUIRED,
icv.[LINKEDREASONCODEID] AS LINKEDREASONCODEID,
icv.[RANDOMFACTOR] AS RANDOMFACTOR,
icv.[RETAILUSEINFOCODE] AS RETAILUSEINFOCODE,
icv.[PRIORITY] AS PRIORITY,
COALESCE(rict.[DESCRIPTION], rictd.[DESCRIPTION], icv.[REASONCODEID]) AS DESCRIPTION,
COALESCE(rict.[PROMPT], rictd.[PROMPT], icv.[REASONCODEID]) AS PROMPT,
COALESCE(rict.[LANGUAGEID], rictd.[LANGUAGEID]) AS LANGUAGEID
FROM [crt].[INFOCODEVIEW] icv
LEFT JOIN [ax].[RETAILINFOCODETRANSLATION] rict
ON icv.[RECID] = rict.[INFOCODE]
AND icv.[DATAAREAID] = rict.[DATAAREAID]
AND rict.[LANGUAGEID] = @languageId
LEFT JOIN [ax].[RETAILINFOCODETRANSLATION] rictd
ON icv.[RECID] = rictd.[INFOCODE]
AND icv.[DATAAREAID] = rictd.[DATAAREAID]
AND rictd.[LANGUAGEID] = @defaultlanguageId
WHERE icv.[DATAAREAID] = @DataAreaId
AND ((SELECT COUNT(STRINGID) FROM @tvp_groupIds) = 0 OR icv.[GROUPID] IN (SELECT STRINGID FROM @tvp_groupIds))
)";
SqlPagedQuery query = new SqlPagedQuery(this.request.QueryResultSettings)
{
From = GetReasonCodesQueryString,
Aliased = true,
DatabaseSchema = string.Empty,
OrderBy = new SortingInfo(ReasonCode.PriorityColumn, false).ToString()
};
PagedResult <ReasonCode> reasonCodes;
query.Parameters["@defaultlanguageId"] = this.defaultLanguageId;
query.Parameters["@languageId"] = this.employeeLanguageId;
query.Parameters["@DataAreaId"] = this.request.RequestContext.GetChannelConfiguration().InventLocationDataAreaId;
using (SqliteDatabaseContext context = new SqliteDatabaseContext(this.request.RequestContext))
using (StringIdTableType groupIds = new StringIdTableType(this.request.ReasonCodeIds, "GROUPID"))
{
// the view sets the INFOCODEID to GROUPID when the reason code is not part of a group, so we always query by GROUPID
query.Parameters["@tvp_groupIds"] = groupIds.DataTable;
reasonCodes = context.ReadEntity <ReasonCode>(query);
if (reasonCodes.Results.Any())
{
IEnumerable <string> reasonCodeIds = reasonCodes.Results.Select(x => x.ReasonCodeId);
QueryResultSettings subCodeSettings = QueryResultSettings.AllRecords;
GetSubReasonCodesProcedure getSubReasonCodes = new GetSubReasonCodesProcedure(
this.request.RequestContext,
context,
reasonCodeIds,
reasonSubCodeId: null,
settings: subCodeSettings,
defaultlanguageId: this.defaultLanguageId,
employeeLanguageId: this.employeeLanguageId);
ILookup <string, ReasonSubCode> subcodes = getSubReasonCodes.Execute().ToLookup(x => x.ReasonCodeId);
foreach (var infoCode in reasonCodes.Results)
{
infoCode.ReasonSubCodes.Clear();
infoCode.ReasonSubCodes.AddRange(subcodes[infoCode.ReasonCodeId]);
}
}
}
return(reasonCodes);
}
