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 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 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)); }
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 static EntityDataServiceResponse <SalesOrder> GetSalesTransaction(GetSalesTransactionDataRequest request) { ThrowIf.Null(request, "request"); ThrowIf.Null(request.QueryResultSettings, "request.QueryResultSettings"); ThrowIf.Null(request.SearchCriteria, "request.SearchCriteria"); var query = new SqlPagedQuery(request.QueryResultSettings) { From = SearchSalesOrdersFunctionTemplate, }; if (request.QueryResultSettings.Sorting == null || request.QueryResultSettings.Sorting.Count == 0) { query.OrderBy = new SortingInfo(CreatedDateTimeColumn, true).ToString(); } var whereClauses = new List <string>(); BuildWhereClauseForItemIdBarcodeSerialNumber(request.SearchCriteria, query, whereClauses, request.RequestContext.Runtime.Configuration.DatabaseProvider.GetDatabaseQueryBuilder()); BuildSearchOrderWhereClause(request.SearchCriteria, query, whereClauses); query.Where = string.Join(" AND ", whereClauses); string customerName = "\"\""; if (!string.IsNullOrEmpty(request.SearchCriteria.CustomerFirstName) || !string.IsNullOrEmpty(request.SearchCriteria.CustomerLastName)) { customerName = request.SearchCriteria.CustomerFirstName == null ? "\"" + request.SearchCriteria.CustomerLastName + "\"" : "\"" + request.SearchCriteria.CustomerFirstName + "*" + (request.SearchCriteria.CustomerLastName ?? string.Empty) + "\""; } query.Parameters["@nvc_CustomerName"] = customerName; PagedResult <SalesOrder> transactions; using (SqlServerDatabaseContext sqlServerDatabaseContext = new SqlServerDatabaseContext(request)) { transactions = sqlServerDatabaseContext.ReadEntity <SalesOrder>(query); } return(new EntityDataServiceResponse <SalesOrder>(transactions)); }
private EntityDataServiceResponse <ChannelCategoryAttribute> GetChannelCategoryAttributes(GetChannelCategoryAttributesDataRequest request) { PagedResult <ChannelCategoryAttribute> channelCategoryAttributes; var query = new SqlPagedQuery(request.QueryResultSettings) { From = ChannelCategoryAttributeViewName, Where = "HOSTCHANNEL = " + SqlParamChannelId, OrderBy = "HOSTCHANNEL, CATEGORY, KEYNAME" }; using (RecordIdTableType categoryRecordIds = new RecordIdTableType(request.CategoryIds, "CATEGORY")) using (SqlServerDatabaseContext context = new SqlServerDatabaseContext(request)) { query.Parameters[SqlParamChannelId] = request.ChannelId; query.Parameters["@TVP_RECIDTABLETYPE"] = categoryRecordIds; channelCategoryAttributes = context.ReadEntity <ChannelCategoryAttribute>(query); } return(new EntityDataServiceResponse <ChannelCategoryAttribute>(channelCategoryAttributes)); }
private EntityDataServiceResponse <DropAndDeclareTransaction> GetDropAndDeclareTransactions(GetDropAndDeclareTransactionDataRequest request) { ThrowIf.NullOrWhiteSpace(request.DropAndDeclareTransactionId, "request.DropAndDeclareTransactionId"); ThrowIf.Null(request.QueryResultSettings, "request.QueryResultSettings"); var query = new SqlPagedQuery(request.QueryResultSettings) { From = DropAndDeclareTransactionsView, Where = "TRANSACTIONID = @TransactionId", }; query.Parameters["@TransactionId"] = request.DropAndDeclareTransactionId; PagedResult <DropAndDeclareTransaction> dropAndDeclareTransaction; using (var sqlServerDatabaseContext = new SqlServerDatabaseContext(request)) { dropAndDeclareTransaction = sqlServerDatabaseContext.ReadEntity <DropAndDeclareTransaction>(query); } return(new EntityDataServiceResponse <DropAndDeclareTransaction>(dropAndDeclareTransaction)); }
private EntityDataServiceResponse <TenderDetail> GetDropAndDeclareTransactionTenderDetails(GetDropAndDeclareTransactionTenderDetailsDataRequest request) { ThrowIf.NullOrWhiteSpace(request.DropAndDeclareTransactionId, "request.DropAndDeclareTransactionId"); ThrowIf.Null(request.QueryResultSettings, "request.QueryResultSettings"); var query = new SqlPagedQuery(request.QueryResultSettings) { From = DropAndDeclareTendersView, Where = "TRANSACTIONID = @TransactionId", OrderBy = "LINENUM", }; query.Parameters["@TransactionId"] = request.DropAndDeclareTransactionId; PagedResult <TenderDetail> results; using (var sqlServerDatabaseContext = new SqlServerDatabaseContext(request)) { results = sqlServerDatabaseContext.ReadEntity <TenderDetail>(query); } return(new EntityDataServiceResponse <TenderDetail>(results)); }
/// <summary> /// Gets the collection of stores within the specified search area. /// </summary> /// <param name="request">The get stores data request.</param> /// <returns> /// A collection of stores. /// </returns> private EntityDataServiceResponse <OrgUnitLocation> GetStores(GetStoresDataRequest request) { ThrowIf.Null(request, "request"); ThrowIf.Null(request.SearchArea, "request.SearchArea"); ThrowIf.Null(request.QueryResultSettings, "request.QueryResultSettings"); QueryResultSettings settings = request.QueryResultSettings; SearchArea searchArea = request.SearchArea; if (settings.ColumnSet != null && settings.ColumnSet.Count > 0 && !settings.ColumnSet.Contains(OrgUnitLocation.DistanceColumn)) { settings.ColumnSet.Add(OrgUnitLocation.DistanceColumn); } var query = new SqlPagedQuery(settings) { From = GetNearbyStoresFunctionName, OrderBy = searchArea.IsUnbounded ? "NAME ASC" : "DISTANCE ASC" }; query.Parameters[ChannelIdVariable] = request.ChannelId; query.Parameters[LongitudeVariable] = searchArea.Longitude; query.Parameters[LatitudeVariable] = searchArea.Latitude; query.Parameters[SearchDistanceVariable] = searchArea.Radius; query.Parameters[UnitConversionVariable] = searchArea.GetUnitConversion(); PagedResult <OrgUnitLocation> storeLocationsRecords; using (var sqlServerdatabaseContext = new SqlServerDatabaseContext(request)) { storeLocationsRecords = sqlServerdatabaseContext.ReadEntity <OrgUnitLocation>(query); } storeLocationsRecords = this.ParseLocations(storeLocationsRecords.Results, request.RequestContext); return(new EntityDataServiceResponse <OrgUnitLocation>(storeLocationsRecords)); }
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)); }