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