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