/// <summary>
/// Gets order history for customer.
/// </summary>
/// <param name="request">The data service request.</param>
/// <returns>The data service response.</returns>
private static EntityDataServiceResponse <SalesOrder> GetOrderHistory(GetOrderHistoryDataRequest request)
{
ThrowIf.Null(request, "request");
ThrowIf.Null(request.RequestContext, "request.RequestContext");
ThrowIf.NullOrWhiteSpace(request.CustomerAccountNumber, "request.CustomerAccountNumber");
ThrowIf.Null(request.QueryResultSettings, "request.QueryResultSettings");
// Build the where clause
var query = new SqlPagedQuery(request.QueryResultSettings)
{
From = OrderHistoryView,
Where = RetailTransactionTableSchema.CustomerIdColumn + " = " + CustomerAccountNumberVariableName + " AND " +
RetailTransactionTableSchema.CreatedDateTimeColumn + " >= " + StartDateVariableName,
OrderBy = RetailTransactionTableSchema.CreatedDateTimeColumn + " DESC"
};
query.Parameters[CustomerAccountNumberVariableName] = request.CustomerAccountNumber;
query.Parameters[StartDateVariableName] = request.StartDateTime;
using (DatabaseContext databaseContext = new DatabaseContext(request.RequestContext))
{
PagedResult <SalesOrder> results = databaseContext.ReadEntity <SalesOrder>(query);
SalesTransactionDataService.FillSalesOrderMembers(results.Results, true, request.RequestContext);
return(new EntityDataServiceResponse <SalesOrder>(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 void FilterChargeConfigurations(SqlPagedQuery query, ChargeLevel chargeType, ChargeConfigurationHeader header)
{
StringBuilder whereClause = new StringBuilder("(");
whereClause.Append("(MODULECATEGORY = @ChargeType)");
if (header.AccountType != ChargeAccountType.None)
{
whereClause.Append(" AND (ACCOUNTCODE = @AccountType AND ACCOUNTRELATION = @AccountRelation) ");
}
if (header.ItemType != ChargeItemType.None)
{
whereClause.Append(" AND (ITEMCODE = @ItemType AND ITEMRELATION = @ItemRelation AND MODULETYPE = 1) ");
}
if (header.DeliveryType != ChargeDeliveryType.None)
{
whereClause.Append(" AND (DLVMODECODE = @DeliveryType AND DLVMODERELATION = @DeliveryRelation AND MODULETYPE = 3) ");
}
whereClause.Append(")");
query.Where = whereClause.ToString();
query.Parameters["@ChargeType"] = chargeType;
query.Parameters["@AccountType"] = header.AccountType;
query.Parameters["@AccountRelation"] = header.AccountRelation;
query.Parameters["@ItemType"] = header.ItemType;
query.Parameters["@ItemRelation"] = header.ItemRelation;
query.Parameters["@DeliveryType"] = header.DeliveryType;
query.Parameters["@DeliveryRelation"] = header.DeliveryRelation;
}
private static GetUnitOfMeasureConversionDataResponse GetUnitOfMeasureConversion(GetUnitOfMeasureConversionDataRequest request)
{
ThrowIf.Null(request, "request");
ThrowIf.Null(request.ItemUnitConversions, "request.ItemUnitConversions");
ThrowIf.Null(request.QueryResultSettings, "request.QueryResultSettings");
var query = new SqlPagedQuery(request.QueryResultSettings)
{
From = GetUnitOfMeasureConversionsFunctionName,
OrderBy = "ITEMID, FROMUNITID, TOUNITID",
};
PagedResult <UnitOfMeasureConversion> unitOfMeasureConversions;
using (ItemUnitConversionTableType type = new ItemUnitConversionTableType(request.ItemUnitConversions))
{
query.Parameters[DataAreaIdVariableName] = request.RequestContext.GetChannelConfiguration().InventLocationDataAreaId;
query.Parameters[ItemUnitConversionsTableType] = type.DataTable;
using (var sqlServerDatabaseContext = new SqlServerDatabaseContext(request))
{
unitOfMeasureConversions = sqlServerDatabaseContext.ReadEntity <UnitOfMeasureConversion>(query);
}
}
return(new GetUnitOfMeasureConversionDataResponse(unitOfMeasureConversions));
}
/// <summary>
/// Gets Receipt Header information for India.
/// </summary>
/// <param name="request">The get receipt header info India data request.</param>
/// <returns>Receipt Header information.</returns>
private SingleEntityDataServiceResponse <ReceiptHeaderInfoIndia> GetReceiptHeaderInfoIndia(GetReceiptHeaderInfoIndiaDataRequest request)
{
ThrowIf.Null(request, "request");
IndiaTaxL2CacheDataStoreAccessor level2CacheDataAccessor = this.GetIndiaTaxL2CacheDataStoreAccessor(request.RequestContext);
bool found;
bool updateL2Cache;
ReceiptHeaderInfoIndia result = DataManager.GetDataFromCache(() => level2CacheDataAccessor.GetReceiptHeaderInfoIndia(request.QueryResultSettings.ColumnSet), out found, out updateL2Cache);
if (!found)
{
var query = new SqlPagedQuery(request.QueryResultSettings)
{
From = TaxInformationLegalEntitiesIndiaViewName,
Where = "CHANNELID = @channelId",
};
query.Parameters["@channelId"] = request.RequestContext.GetPrincipal().ChannelId;
using (DatabaseContext databaseContext = new DatabaseContext(request.RequestContext))
{
result = databaseContext.ReadEntity <ReceiptHeaderInfoIndia>(query).SingleOrDefault();
}
updateL2Cache &= result != null;
}
if (updateL2Cache)
{
level2CacheDataAccessor.PutReceiptHeaderInfoIndia(request.QueryResultSettings.ColumnSet, result);
}
return(new SingleEntityDataServiceResponse <ReceiptHeaderInfoIndia>(result));
}
private static EntityDataServiceResponse <ListingPublishStatus> GetListingPublishStatuses(GetListingPublishStatusesDataRequest request)
{
var productIds = request.ListingIds;
ThrowIf.Null(productIds, "productIds");
var settings = QueryResultSettings.AllRecords;
var query = new SqlPagedQuery(settings)
{
Select = request.QueryResultSettings.ColumnSet,
From = ListingPublishStatusViewName,
};
PagedResult <ListingPublishStatus> listingPublishStatuses;
using (RecordIdTableType type = new RecordIdTableType(productIds, ProductColumnName))
{
query.Parameters["@TVP_RECORDIDTABLETYPE"] = type;
using (DatabaseContext databaseContext = new DatabaseContext(request.RequestContext))
{
listingPublishStatuses = databaseContext.ReadEntity <ListingPublishStatus>(query);
}
}
return(new EntityDataServiceResponse <ListingPublishStatus>(listingPublishStatuses));
}
private static PagedResult <ProductSearchResult> SearchByText(long channelId, long catalogId, DateTime channelDateTime, string locale, string searchText, bool useFuzzySearch, RequestContext context, QueryResultSettings settings)
{
var query = new SqlPagedQuery(settings)
{
From = SearchProductsBytextFunctionName
};
query.Parameters[DatabaseAccessor.ChannelIdVariableName] = channelId;
query.Parameters[CatalogIdVariableName] = catalogId;
query.Parameters[DatabaseAccessor.ChannelDateVariableName] = channelDateTime;
query.Parameters[MaxTopVariableName] = (int)(settings.Paging.NumberOfRecordsToFetch + settings.Paging.Skip);
query.Parameters[LocaleVariableName] = locale;
query.Parameters[SearchTextVariableName] = new FreeTextSearchFormatter(searchText)
{
UseFuzzySearch = useFuzzySearch
}.GetFormattedSearchText();
// This hint ensures efficient parameter sniffing resulting in only parts of the SQL code relevant to this specific request being executed.
// In this case, when catalog id is zero, all catalog related SQL operation(s) are completely eliminated from the execution plan.
query.AddHints("RECOMPILE");
using (DatabaseContext databaseContext = new DatabaseContext(context))
{
return(databaseContext.ReadEntity <ProductSearchResult>(query));
}
}
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>
/// Checks whether the current employee has an open session on the current terminal.
/// </summary>
/// <param name="requestContext">The request context.</param>
/// <returns>The data service response.</returns>
private SingleEntityDataServiceResponse <bool> CheckEmployeeHasOpenSessionOnCurrentTerminal(RequestContext requestContext)
{
const string EmployeeSessionsViewName = "EmployeeSessionsView";
string terminalId = requestContext.GetTerminal().TerminalId;
string staffId = requestContext.GetPrincipal().UserId;
bool employeeHasOpenSessionOnCurrentTerminal;
DataStoreManager.InstantiateDataStoreManager(requestContext);
EmployeeL2CacheDataStoreAccessor accessor = new EmployeeL2CacheDataStoreAccessor(DataStoreManager.DataStores[DataStoreType.L2Cache], requestContext);
if (!accessor.CheckEmployeeSessionOpenOnTerminal(terminalId, staffId, out employeeHasOpenSessionOnCurrentTerminal))
{
using (DatabaseContext databaseContext = new DatabaseContext(requestContext))
{
SqlPagedQuery query = new SqlPagedQuery(QueryResultSettings.SingleRecord)
{
Select = new ColumnSet("STAFFID"),
From = EmployeeSessionsViewName,
Where = "DATAAREAID = @DATAAREAID AND STAFFID = @STAFFID AND TERMINALID = @TERMINALID AND CHANNELID = @CHANNELID",
};
query.Parameters["@DATAAREAID"] = requestContext.GetChannelConfiguration().InventLocationDataAreaId;
query.Parameters["@STAFFID"] = staffId;
query.Parameters["@TERMINALID"] = terminalId;
query.Parameters["@CHANNELID"] = requestContext.GetPrincipal().ChannelId;
employeeHasOpenSessionOnCurrentTerminal = databaseContext.ExecuteScalarCollection <string>(query).Any();
}
accessor.CacheIsEmployeeSessionOpenOnTerminal(terminalId, staffId, employeeHasOpenSessionOnCurrentTerminal);
}
return(new SingleEntityDataServiceResponse <bool>(employeeHasOpenSessionOnCurrentTerminal));
}
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));
}
private PagedResult <T> ExecuteQuery <T>(SqlPagedQuery query, RequestContext context) where T : CommerceEntity, new()
{
PagedResult <T> results;
using (var databaseContext = new DatabaseContext(context))
{
results = databaseContext.ReadEntity <T>(query);
}
return(results);
}
private PagedResult <UnitOfMeasure> ExecuteQuery(SqlPagedQuery query, RequestContext context)
{
PagedResult <UnitOfMeasure> results;
using (var databaseContext = new DatabaseContext(context))
{
results = databaseContext.ReadEntity <UnitOfMeasure>(query);
}
return(results);
}
private static EntityDataServiceResponse <ProductAttributeSchemaEntry> GetProductMediaAttributeSchemaEntries(GetProductMediaAttributeSchemaEntriesDataRequest request)
{
var query = new SqlPagedQuery(request.QueryResultSettings)
{
From = MediaAttributesViewName
};
using (DatabaseContext databaseContext = new DatabaseContext(request.RequestContext))
{
return(new EntityDataServiceResponse <ProductAttributeSchemaEntry>(databaseContext.ReadEntity <ProductAttributeSchemaEntry>(query)));
}
}
/// <summary>
/// Gets pending transactions count.
/// </summary>
/// <returns>The GetOfflinePendingTransactionCount response message.</returns>
public GetOfflinePendingTransactionCountDataResponse GetPendingTransactionCount()
{
var query = new SqlPagedQuery(this.request.QueryResultSettings)
{
From = RetailOfflinePendingTransactionCountView,
OrderBy = RetailOfflinePendingTransactionCountColumn
};
long numberOfTransactions = (long)this.databaseContext.ExecuteScalar <int>(query);
return(new GetOfflinePendingTransactionCountDataResponse(numberOfTransactions));
}
/// <summary>
/// Retrieves the OfflineSyncStatsLine entity data service response.
/// </summary>
/// <returns>The entity data service response.</returns>
public EntityDataServiceResponse <OfflineSyncStatsLine> Execute()
{
var query = new SqlPagedQuery(this.request.QueryResultSettings)
{
From = RetailOfflineSyncStatsView,
OrderBy = RetailOfflineSyncStatsSorting
};
PagedResult <OfflineSyncStatsLine> results = this.databaseContext.ReadEntity <OfflineSyncStatsLine>(query);
return(new EntityDataServiceResponse <OfflineSyncStatsLine>(results));
}
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));
}
}
/// <summary>
/// Gets the last closed shift.
/// </summary>
/// <param name="request">The request.</param>
/// <returns>A single entity data service response.</returns>
private SingleEntityDataServiceResponse <Shift> GetLastClosedShift(GetLastClosedShiftDataRequest request)
{
ThrowIf.Null(request, "request");
ThrowIf.NullOrWhiteSpace(request.TerminalId, "request.TerminalId");
long channelId = request.RequestContext.GetPrincipal().ChannelId;
var query = new SqlPagedQuery(QueryResultSettings.FirstRecord)
{
From = ShiftsView,
Where = "CHANNEL = @ChannelId AND CLOSEDATTERMINAL = @TerminalId AND STATUS = @Status",
OrderBy = "CLOSEDATETIMEUTC DESC"
};
query.Parameters["@ChannelId"] = channelId;
query.Parameters["@TerminalId"] = request.TerminalId;
query.Parameters["@Status"] = (int)ShiftStatus.Closed;
Shift lastShift = null;
using (var sqlServerDatabaseContext = new SqlServerDatabaseContext(request))
{
lastShift = sqlServerDatabaseContext.ReadEntity <Shift>(query).Results.FirstOrDefault();
}
if (lastShift != null)
{
GetShiftDataRequest getShiftDataRequest = new GetShiftDataRequest(lastShift.TerminalId, lastShift.ShiftId);
lastShift = request.RequestContext.Execute <SingleEntityDataServiceResponse <Shift> >(getShiftDataRequest).Entity;
// Convert UTC time to channel time.
if (lastShift.StartDateTime != null)
{
lastShift.StartDateTime = new DateTimeOffset(lastShift.StartDateTime.Value.DateTime, new TimeSpan(0));
}
if (lastShift.StatusDateTime != null)
{
lastShift.StatusDateTime = new DateTimeOffset(lastShift.StatusDateTime.Value.DateTime, new TimeSpan(0));
}
if (lastShift.CloseDateTime != null)
{
lastShift.CloseDateTime = new DateTimeOffset(lastShift.CloseDateTime.Value.DateTime, new TimeSpan(0));
}
}
return(new SingleEntityDataServiceResponse <Shift>(lastShift));
}
public EntityDataServiceResponse <DiscountLine> Execute()
{
var query = new SqlPagedQuery(this.request.QueryResultSettings)
{
From = RetailTransactionDiscountTransView,
Where = "TRANSACTIONID = @TransactionId and SALELINENUM = @SaleLineNumber",
};
query.Parameters["@TransactionId"] = this.request.Criteria.TransactionId;
query.Parameters["@SaleLineNumber"] = this.request.Criteria.LineNumber;
PagedResult <DiscountLine> results = this.databaseContext.ReadEntity <DiscountLine>(query);
return(new EntityDataServiceResponse <DiscountLine>(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>
/// Looks up for the matching user credential against the database.
/// </summary>
/// <param name="request">The request.</param>
/// <returns>The data service response.</returns>
private SingleEntityDataServiceResponse <UserCredential> GetUserCredential(GetUserCredentialsDataRequest request)
{
using (DatabaseContext databaseContext = new DatabaseContext(request.RequestContext))
{
SqlPagedQuery query = new SqlPagedQuery(QueryResultSettings.SingleRecord)
{
From = StaffCredentialsView,
Where = "CREDENTIALID = @CREDENTIALID AND GRANTTYPE = @GRANTTYPE",
};
query.Parameters["@CREDENTIALID"] = request.CredentialId;
query.Parameters["@GRANTTYPE"] = request.GrantType;
return(new SingleEntityDataServiceResponse <UserCredential>(databaseContext.ReadEntity <UserCredential>(query).Results.FirstOrDefault()));
}
}
/// <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));
}
private PagedResult <ProductVariant> Execute(string inputTableName, string fromClause)
{
string fromQuery = string.Format(fromClause, inputTableName);
string query = string.Format(QueryBody, fromQuery);
var sqlQuery = new SqlPagedQuery(this.settings)
{
From = query,
Aliased = true,
DatabaseSchema = string.Empty
};
sqlQuery.Parameters["@LANGUAGEID"] = this.languageId;
sqlQuery.Parameters["@DATAAREAID"] = this.context.DataAreaId;
return(this.context.ReadEntity <ProductVariant>(sqlQuery));
}
private static EntityDataServiceResponse <ProductDimension> GetProductDimensions(GetProductDimensionsDataRequest request)
{
var query = new SqlPagedQuery(request.QueryResultSettings)
{
Distinct = true, // Need to perform distinct to avoid retrieving a row for every variant type product combination.
Aliased = true,
From = ProductDimensionsViewName
};
using (DatabaseContext databaseContext = new DatabaseContext(request.RequestContext))
using (RecordIdTableType productIds = new RecordIdTableType(request.ProductIds, ProductIdVariableName))
{
query.Parameters[ProductIdsVariableName] = productIds;
return(new EntityDataServiceResponse <ProductDimension>(databaseContext.ReadEntity <ProductDimension>(query)));
}
}
/// <summary>
/// Gets the shift tender lines or account lines entity.
/// </summary>
/// <param name="userId">The user identifier.</param>
/// <param name="viewName">The view name.</param>
/// <param name="terminalId">The terminal identifier.</param>
/// <param name="shiftId">The shift identifier.</param>
/// <param name="request">The request.</param>
/// <param name="queryByPrimaryKey">The query by primary key flag.</param>
/// <typeparam name="T">The entity type.</typeparam>
/// <returns>A collection of shift tender lines or account lines.</returns>
private PagedResult <T> GetShiftEntity <T>(string userId, string viewName, string terminalId, long shiftId, Request request, bool queryByPrimaryKey) where T : CommerceEntity, new()
{
var settings = QueryResultSettings.AllRecords;
if (queryByPrimaryKey)
{
settings = QueryResultSettings.SingleRecord;
}
var query = new SqlPagedQuery(settings)
{
From = viewName,
};
var whereClauses = new List <string>();
whereClauses.Add("CHANNEL = @ChannelId");
whereClauses.Add("TERMINALID = @TerminalId");
whereClauses.Add("SHIFTID = @ShiftId");
query.Parameters["@ChannelId"] = request.RequestContext.GetPrincipal().ChannelId;
query.Parameters["@TerminalId"] = terminalId;
query.Parameters["@ShiftId"] = shiftId;
if (!string.IsNullOrEmpty(userId))
{
whereClauses.Add(@"(STAFFID = @StaffId OR CURRENTSTAFFID = @StaffId)");
query.Parameters["@StaffId"] = userId;
}
// Compose the where clause
if (whereClauses.Count != 0)
{
query.Where = string.Join(" AND ", whereClauses);
}
// Load the shift entity
PagedResult <T> shiftEntity = null;
using (DatabaseContext databaseContext = new DatabaseContext(request.RequestContext))
{
shiftEntity = databaseContext.ReadEntity <T>(query);
}
return(shiftEntity);
}
/// <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);
}
private static EntityDataServiceResponse <ProductVariant> GetProductVariants(GetProductVariantsDataRequest request)
{
IEnumerable <ItemVariantInventoryDimension> itemVariants = request.ItemAndInventoryDimensionIds;
RequestContext context = request.RequestContext;
ThrowIf.Null(itemVariants, "itemVariants");
ThrowIf.Null(context, "context");
ColumnSet columnSet = new ColumnSet();
ItemL2CacheDataStoreAccessor level2CacheDataAccessor = GetCacheAccessor(context);
bool found;
bool updateL2Cache;
ReadOnlyCollection <ProductVariant> result = DataManager.GetDataFromCache(() => level2CacheDataAccessor.GetVariants(itemVariants, columnSet), out found, out updateL2Cache);
if (!found)
{
var query = new SqlPagedQuery(QueryResultSettings.AllRecords)
{
Select = columnSet,
From = GetVariantsByItemIdAndInventDimIdFunctionName
};
using (var type = new ItemVariantInventoryDimensionTableType(itemVariants))
{
query.Parameters[DatabaseAccessor.ChannelIdVariableName] = context.GetPrincipal().ChannelId;
query.Parameters[DatabaseAccessor.ChannelDateVariableName] = context.GetNowInChannelTimeZone().Date;
query.Parameters[ItemVariantIdsVariableName] = type.DataTable;
using (SqlServerDatabaseContext databaseContext = new SqlServerDatabaseContext(context))
{
result = databaseContext.ReadEntity <ProductVariant>(query).Results;
}
}
updateL2Cache &= result != null;
}
if (updateL2Cache)
{
level2CacheDataAccessor.PutVariants(itemVariants, columnSet, result);
}
return(new EntityDataServiceResponse <ProductVariant>(result.AsPagedResult()));
}
private SingleEntityDataServiceResponse <ChargeLine> GetChargeDetails(GetChargeLinesDataRequest request)
{
ThrowIf.Null(request, "request");
ThrowIf.Null(request.QueryResultSettings, "request.QueryResultSettings");
ThrowIf.Null(request.ChargeCode, "request.ChargeCode");
ChargeL2CacheDataStoreAccessor level2CacheDataAccessor = this.GetChargeL2CacheDataStoreAccessor(request.RequestContext);
bool found;
bool updateL2Cache;
string dataAreaId = request.RequestContext.GetChannelConfiguration().InventLocationDataAreaId;
ChargeLine result = DataManager.GetDataFromCache(() => level2CacheDataAccessor.GetChargeDetails(dataAreaId, request.ChargeCode, request.ChargeModule, request.QueryResultSettings), out found, out updateL2Cache);
if (!found)
{
var query = new SqlPagedQuery(request.QueryResultSettings)
{
From = ChargesView,
Where = "MARKUPCODE = @MarkupCode AND MODULETYPE = @ModuleType And DATAAREAID = @DataAreaId"
};
query.Parameters["@MarkupCode"] = request.ChargeCode;
query.Parameters["@ModuleType"] = request.ChargeModule;
query.Parameters["@DataAreaId"] = dataAreaId;
using (DatabaseContext databaseContext = new DatabaseContext(request.RequestContext))
{
result = databaseContext.ReadEntity <ChargeLine>(query).FirstOrDefault();
}
if (result == null)
{
result = new ChargeLine();
}
updateL2Cache &= result != null;
}
if (updateL2Cache)
{
level2CacheDataAccessor.PutChargeDetails(dataAreaId, request.ChargeCode, request.ChargeModule, request.QueryResultSettings, result);
}
return(new SingleEntityDataServiceResponse <ChargeLine>(result));
}
private GetStoreHoursDataResponse GetStoreDayHours(GetStoreHoursDataRequest request)
{
ThrowIf.Null(request, "request");
using (DatabaseContext databaseContext = new DatabaseContext(request.RequestContext))
{
var query = new SqlPagedQuery(request.QueryResultSettings)
{
DatabaseSchema = "crt",
Select = new ColumnSet("DAY", "OPENTIME", "CLOSINGTIME", "RECID"),
From = "ISVRETAILSTOREHOURSVIEW",
Where = "STORENUMBER = @storeNumber",
};
query.Parameters["@storeNumber"] = request.StoreNumber;
return(new GetStoreHoursDataResponse(databaseContext.ReadEntity <DataModel.StoreDayHours>(query)));
}
}
private OfflineDatabaseChunk GetOfflineDatabaseChunkByRecordId(long recordId, QueryResultSettings settings, RequestContext requestContext)
{
var query = new SqlPagedQuery(settings)
{
From = OfflineDatabaseChunkViewName,
Where = "RECID = @RecordId"
};
query.Parameters["@RecordId"] = recordId;
OfflineDatabaseChunk chunk = null;
using (DatabaseContext databaseContext = new DatabaseContext(requestContext))
{
chunk = databaseContext.ReadEntity <OfflineDatabaseChunk>(query).SingleOrDefault();
}
return(chunk);
}