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));
}
/// <summary>
/// Executes the get products by category procedure.
/// </summary>
/// <returns>The collection of product identifiers.</returns>
public ReadOnlyCollection <long> Execute()
{
var categoryIdsTableType = new RecordIdTableType(this.categoryIds);
using (TempTable tmpCategoryIds = this.context.CreateTemporaryTable(categoryIdsTableType.DataTable))
{
var query = new SqlQuery(QueryCommand, categoryIdsTableType.DataTable.TableName);
query.Parameters["@bi_ChannelId"] = this.channelId;
query.Parameters["@dt_ChannelDate"] = this.context.ChannelDateTimeNow;
return(this.context.ExecuteScalarCollection <long>(query));
}
}
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)));
}
}
private static EntityDataServiceResponse <RetailCategoryMember> GetRetailCategoryMembersForItems(GetRetailCategoryMembersForItemsDataRequest request)
{
RequestContext context = request.RequestContext;
ISet <long> productOrVariantIds = request.ProductOrVariantIds;
ThrowIf.Null(productOrVariantIds, "productOrVariantIds");
ThrowIf.Null(context, "context");
ItemL2CacheDataStoreAccessor level2CacheDataAccessor = GetCacheAccessor(context);
bool found;
bool updateL2Cache;
ReadOnlyCollection <RetailCategoryMember> result = DataManager.GetDataFromCache(() => level2CacheDataAccessor.GetRetailCategoryMembersForItems(productOrVariantIds), out found, out updateL2Cache);
if (!found)
{
SqlPagedQuery query = new SqlPagedQuery(QueryResultSettings.AllRecords)
{
Select = new ColumnSet(),
From = RetailCategoryMembersForItemsViewName
};
using (RecordIdTableType type = new RecordIdTableType(productOrVariantIds, CommerceEntityExtensions.GetColumnName <RetailCategoryMember>(e => e.ProductOrVariantId)))
{
query.Parameters[RecordIdTableTypeVariableName] = type;
using (DatabaseContext databaseContext = new DatabaseContext(context))
{
result = databaseContext.ReadEntity <RetailCategoryMember>(query).Results;
}
}
updateL2Cache &= result != null;
}
if (updateL2Cache)
{
level2CacheDataAccessor.PutRetailCategoryMembersForItems(productOrVariantIds, result);
}
return(new EntityDataServiceResponse <RetailCategoryMember>(result.AsPagedResult()));
}
private static EntityDataServiceResponse <UnitOfMeasure> GetUnitsOfMeasureOfProducts(GetUnitsOfMeasureOfProductsDataRequest request)
{
ThrowIf.Null(request, "request");
ThrowIf.Null(request.QueryResultSettings, "request.QueryResultSettings");
var query = new SqlPagedQuery(request.QueryResultSettings)
{
Select = new ColumnSet(new string[] { "RECID", "SYMBOL", "DECIMALPRECISION", "DESCRIPTION" }),
Aliased = true,
From = ProductIdsToUnitsOfMeasureView
};
using (DatabaseContext databaseContext = new DatabaseContext(request.RequestContext))
using (RecordIdTableType productIds = new RecordIdTableType(request.ProductIds, ProductColumnName))
{
query.Parameters[ProductIdsVariableName] = productIds;
query.Where = string.Format("DATAAREAID = '{0}' AND (LANGUAGEID IS NULL OR LANGUAGEID = '{1}')", request.RequestContext.GetChannelConfiguration().InventLocationDataAreaId, request.RequestContext.LanguageId);
return(new EntityDataServiceResponse <UnitOfMeasure>(databaseContext.ReadEntity <UnitOfMeasure>(query)));
}
}
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));
}
/// <summary>
/// Gets the items available to the current channel by their product identifiers.
/// </summary>
/// <param name="context">The request context.</param>
/// <param name="productIds">The product identifiers.</param>
/// <param name="settings">The query result settings.</param>
/// <returns>The collection of items or an empty list if no matching record could be found.</returns>
private static ReadOnlyCollection <Item> GetItems(RequestContext context, IEnumerable <long> productIds, QueryResultSettings settings)
{
ThrowIf.Null(context, "context");
ThrowIf.Null(productIds, "productIds");
ThrowIf.Null(settings, "settings");
ItemL2CacheDataStoreAccessor level2CacheDataAccessor = GetCacheAccessor(context);
bool found;
bool updateL2Cache;
ReadOnlyCollection <Item> result = DataManager.GetDataFromCache(() => level2CacheDataAccessor.GetItems(productIds, settings), out found, out updateL2Cache);
if (!found)
{
using (RecordIdTableType type = new RecordIdTableType(productIds, "PRODUCT"))
{
var parameters = new ParameterSet();
parameters[DatabaseAccessor.ChannelIdVariableName] = context.GetPrincipal().ChannelId;
parameters[DatabaseAccessor.ChannelDateVariableName] = context.GetNowInChannelTimeZone().DateTime;
parameters[ProductIdsVariableName] = type.DataTable;
using (SqlServerDatabaseContext databaseContext = new SqlServerDatabaseContext(context, settings))
{
result = databaseContext.ExecuteStoredProcedure <Item>(GetItemsByProductIdsSprocName, parameters).Results;
}
}
updateL2Cache &= result != null &&
result.Count < MaxCachedCollectionSize;
}
if (updateL2Cache)
{
level2CacheDataAccessor.PutItems(productIds, settings, result);
}
return(result);
}
/// <summary>
/// Executes the procedure.
/// </summary>
/// <returns>The collection of channel category attributes.</returns>
public PagedResult <ChannelCategoryAttribute> Execute()
{
PagedResult <ChannelCategoryAttribute> channelCategoryAttributes;
var query = new SqlPagedQuery(this.request.QueryResultSettings)
{
From = ChannelCategoryAttributeViewName,
Where = "HOSTCHANNEL = " + SqlParamChannelId
};
using (SqliteDatabaseContext context = new SqliteDatabaseContext(this.request.RequestContext))
using (RecordIdTableType categoryRecordIds = new RecordIdTableType(this.request.CategoryIds, "CATEGORY"))
{
query.Parameters[SqlParamChannelId] = this.request.ChannelId;
query.Parameters["@TVP_RECIDTABLETYPE"] = categoryRecordIds;
channelCategoryAttributes = context.ReadEntity <ChannelCategoryAttribute>(query);
PopulateCategoryPath(context, channelCategoryAttributes.Results, this.request.ChannelId);
}
return(channelCategoryAttributes);
}
/// <summary>
/// Executes the stored procedure.
/// </summary>
/// <returns>The data response.</returns>
public PagedResult <ProductBehavior> Execute()
{
if (!this.request.ProductIds.Any())
{
return(new PagedResult <ProductBehavior>(new List <ProductBehavior>().AsReadOnly()));
}
using (var databaseContext = new SqliteDatabaseContext(this.request.RequestContext))
using (RecordIdTableType type = new RecordIdTableType(this.request.ProductIds, ProductIdsVariableName))
{
string query = @"
-- Retrieve behvaviors for variant id = 0 products.
SELECT DISTINCT
CASE
WHEN [siv].PRODUCTID IS NULL THEN 0
ELSE 1
END AS HASSERIALNUMBER,
CASE [rit].PROHIBITRETURN_RU
WHEN 0 THEN 1
WHEN 1 THEN 0
END AS ISRETURNALLOWED,
[rit].BLOCKEDONPOS AS ISSALEATPHYSICALSTORESALLOWED,
[rit].QTYBECOMESNEGATIVE AS ISNEGATIVEQUANTITYALLOWED,
CASE [rit].NODISCOUNTALLOWED
WHEN 0 THEN 1
WHEN 1 THEN 0
END AS ISDISCOUNTALLOWED,
[rk].DISASSEMBLYATREGISTERALLOWED AS ISKITDISASSEMBLYALLOWED,
[rit].ZEROPRICEVALID AS ISZEROSALEPRICEALLOWED,
[rit].KEYINGINPRICE AS KEYINPRICE,
[rit].KEYINGINQTY AS KEYINQUANTITY,
[rit].MUSTKEYINCOMMENT AS MUSTKEYINCOMMENT,
[rit].PRINTVARIANTSSHELFLABELS AS MUSTPRINTINDIVIDUALSHELFLABELSFORVARIANTS,
[siv].ISSALESPROCESSACTIVATED AS MUSTPROMPTFORSERIALNUMBERONLYATSALE,
[rit].SCALEITEM AS MUSTWEIGHPRODUCTATSALE,
CASE
WHEN [par].VARIANTID != 0 AND [par].VARIANTID = [erdpv].RECID THEN [par].VARIANTID
WHEN [par].VARIANTID = 0 AND [par].PRODUCTID = [erdpv].PRODUCTMASTER THEN [par].PRODUCTID
ELSE [par].PRODUCTID
END AS PRODUCTID,
[rit].DATETOACTIVATEITEM AS VALIDFROMDATEFORSALEATPHYSICALSTORES,
[rit].DATETOBEBLOCKED AS VALIDTODATEFORSALEATPHYSICALSTORES
FROM @tvp_ProductIds ids
INNER JOIN [crt].PRODUCTASSORTMENTRULES par ON [par].CHANNELID = @bi_ChannelId AND [par].PRODUCTID = [ids].RECID AND [par].VARIANTID = 0 AND [par].ISREMOTE = @i_IsRemote AND @dt_ChannelDate BETWEEN [par].VALIDFROM AND [par].VALIDTO
INNER JOIN [ax].RETAILCHANNELTABLE rct ON [rct].RECID = [par].CHANNELID
INNER JOIN [ax].INVENTTABLE it ON [it].PRODUCT = [par].PRODUCTID AND [rct].INVENTLOCATIONDATAAREAID = [it].DATAAREAID
INNER JOIN [ax].RETAILINVENTTABLE rit ON [rit].ITEMID = [it].ITEMID AND [rit].DATAAREAID = [it].DATAAREAID
LEFT OUTER JOIN [crt].SERIALIZEDITEMSVIEW siv ON [siv].PRODUCTID = [par].PRODUCTID AND [siv].ITEMDATAAREAID = [it].DATAAREAID
LEFT OUTER JOIN [ax].RETAILKIT rk ON [rk].PRODUCTMASTER = [par].PRODUCTID AND [par].VARIANTID = 0
LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT erdpv ON [erdpv].RECID = [par].VARIANTID
UNION ALL
-- Retrieve behvaviors for variant id != 0 products.
SELECT DISTINCT
CASE
WHEN [siv].PRODUCTID IS NULL THEN 0
ELSE 1
END AS HASSERIALNUMBER,
CASE [rit].PROHIBITRETURN_RU
WHEN 0 THEN 1
WHEN 1 THEN 0
END AS ISRETURNALLOWED,
[rit].BLOCKEDONPOS AS ISSALEATPHYSICALSTORESALLOWED,
[rit].QTYBECOMESNEGATIVE AS ISNEGATIVEQUANTITYALLOWED,
CASE [rit].NODISCOUNTALLOWED
WHEN 0 THEN 1
WHEN 1 THEN 0
END AS ISDISCOUNTALLOWED,
[rk].DISASSEMBLYATREGISTERALLOWED AS ISKITDISASSEMBLYALLOWED,
[rit].ZEROPRICEVALID AS ISZEROSALEPRICEALLOWED,
[rit].KEYINGINPRICE AS KEYINPRICE,
[rit].KEYINGINQTY AS KEYINQUANTITY,
[rit].MUSTKEYINCOMMENT AS MUSTKEYINCOMMENT,
[rit].PRINTVARIANTSSHELFLABELS AS MUSTPRINTINDIVIDUALSHELFLABELSFORVARIANTS,
[siv].ISSALESPROCESSACTIVATED AS MUSTPROMPTFORSERIALNUMBERONLYATSALE,
[rit].SCALEITEM AS MUSTWEIGHPRODUCTATSALE,
CASE
WHEN [par].VARIANTID != 0 AND [par].VARIANTID = [erdpv].RECID THEN [par].VARIANTID
WHEN [par].VARIANTID = 0 AND [par].PRODUCTID = [erdpv].PRODUCTMASTER THEN [par].PRODUCTID
ELSE [par].PRODUCTID
END AS PRODUCTID,
[rit].DATETOACTIVATEITEM AS VALIDFROMDATEFORSALEATPHYSICALSTORES,
[rit].DATETOBEBLOCKED AS VALIDTODATEFORSALEATPHYSICALSTORES
FROM @tvp_ProductIds ids
INNER JOIN [crt].PRODUCTASSORTMENTRULES par ON [par].CHANNELID = @bi_ChannelId AND [par].VARIANTID = [ids].RECID AND [par].ISREMOTE = @i_IsRemote AND @dt_ChannelDate BETWEEN [par].VALIDFROM AND [par].VALIDTO
INNER JOIN [ax].RETAILCHANNELTABLE rct ON [rct].RECID = [par].CHANNELID
INNER JOIN [ax].INVENTTABLE it ON [it].PRODUCT = [par].PRODUCTID AND [rct].INVENTLOCATIONDATAAREAID = [it].DATAAREAID
INNER JOIN [ax].RETAILINVENTTABLE rit ON [rit].ITEMID = [it].ITEMID AND [rit].DATAAREAID = [it].DATAAREAID
LEFT OUTER JOIN [crt].SERIALIZEDITEMSVIEW siv ON [siv].PRODUCTID = [par].PRODUCTID AND [siv].ITEMDATAAREAID = [it].DATAAREAID
LEFT OUTER JOIN [ax].RETAILKIT rk ON [rk].PRODUCTMASTER = [par].PRODUCTID AND [par].VARIANTID = 0
LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT erdpv ON [erdpv].RECID = [par].VARIANTID";
long currentChannelId = this.request.RequestContext.GetPrincipal().ChannelId;
SqlQuery sqlQuery = new SqlQuery(query, type.DataTable);
sqlQuery.Parameters[DatabaseAccessor.ChannelIdVariableName] = currentChannelId;
sqlQuery.Parameters[DatabaseAccessor.ChannelDateVariableName] = this.request.RequestContext.GetNowInChannelTimeZone().DateTime;
sqlQuery.Parameters[ProductIdsVariableName] = type.DataTable;
if (this.request.DownloadedProductsFilter.HasValue)
{
if (this.request.DownloadedProductsFilter.Value)
{
sqlQuery.Parameters[IsRemoteVariableName] = 1;
}
else
{
sqlQuery.Parameters[IsRemoteVariableName] = 0;
}
}
return(databaseContext.ReadEntity <ProductBehavior>(sqlQuery));
}
}
public PagedResult <KitComponent> Execute()
{
const string GetKitComponentsQueryString = @"
WITH ExplodedKitComponentVariants AS
(
-- First explode all the kit components/substitutes that are product masters
SELECT DISTINCT
PV.RECID AS KITLINEPRODUCTLISTING,
RKC.QUANTITY AS QUANTITY,
UOM.SYMBOL AS UNIT,
0 AS CHARGE,
MasterProductIds.RECID AS KITLINEPRODUCTMASTERLISTING,
1 AS ISDEFAULTCOMPONENT,
RK.PRODUCTMASTER AS KITPRODUCTMASTERLISTING,
RKC.RECID AS KITLINEIDENTIFIER,
@bi_ChannelId AS CHANNEL,
IT.ITEMID AS ITEMID
FROM {0} AS MasterProductIds -- get all masters
INNER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT PV ON PV.PRODUCTMASTER = MasterProductIds.RECID -- explode variants for the masters
INNER JOIN [ax].RETAILKITCOMPONENT RKC ON RKC.COMPONENT = PV.RECID
INNER JOIN [ax].RETAILKIT RK ON RK.RECID = RKC.KIT
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = PV.PRODUCTMASTER
INNER JOIN [ax].RETAILCHANNELTABLE RCT ON RCT.RECID = @bi_ChannelId
INNER JOIN [ax].UNITOFMEASURE UOM ON UOM.RECID = RKC.UNITOFMEASURE
UNION
SELECT DISTINCT
PV.RECID AS KITLINEPRODUCTLISTING,
RKCS.QUANTITY AS QUANTITY,
UOM.SYMBOL AS UNIT,
IFNULL(RKRSC.SUBSTITUTECHARGE, 0.0) AS CHARGE,
MasterProductIds.RECID AS KITLINEPRODUCTMASTERLISTING,
0 AS ISDEFAULTCOMPONENT,
RK.PRODUCTMASTER AS KITPRODUCTMASTERLISTING,
RKC.RECID AS KITLINEIDENTIFIER,
@bi_ChannelId AS CHANNEL,
IT.ITEMID AS ITEMID
FROM {0} AS MasterProductIds -- get all masters
INNER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT PV ON PV.PRODUCTMASTER = MasterProductIds.RECID -- explode variants for the masters
INNER JOIN [ax].RETAILKITCOMPONENTSUBSTITUTE RKCS ON RKCS.SUBSTITUTEPRODUCT = PV.RECID
INNER JOIN [ax].RETAILKITCOMPONENT RKC ON RKC.RECID = RKCS.KITCOMPONENT
INNER JOIN [ax].RETAILKIT RK ON RK.RECID = RKC.KIT
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = PV.PRODUCTMASTER
INNER JOIN [ax].RETAILCHANNELTABLE RCT ON RCT.RECID = @bi_ChannelId
INNER JOIN [ax].UNITOFMEASURE UOM ON UOM.RECID = RKCS.UNITOFMEASURE
LEFT JOIN [ax].RETAILKITRELEASEDSUBSTITUTECHARGE RKRSC ON RKRSC.KITCOMPONENTSUBSTITUTE = RKCS.RECID AND RKRSC.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
),
KitComponentVariantsInfo AS
(
-- Collect information of all kit components/substitutes that are non product masters.
-- Standalone products
SELECT DISTINCT
RKC.COMPONENT AS KITLINEPRODUCTLISTING,
RKC.QUANTITY AS QUANTITY,
UOM.SYMBOL AS UNIT,
0 AS CHARGE,
StandaloneAndVariantIds.RECID AS KITLINEPRODUCTMASTERLISTING,
1 AS ISDEFAULTCOMPONENT,
RK.PRODUCTMASTER AS KITPRODUCTMASTERLISTING,
RKC.RECID AS KITLINEIDENTIFIER,
@bi_ChannelId AS CHANNEL,
IT.ITEMID AS ITEMID
FROM {1} StandaloneAndVariantIds -- get standalone and variants
INNER JOIN [ax].RETAILKITCOMPONENT RKC ON RKC.COMPONENT = StandaloneAndVariantIds.RECID
INNER JOIN [ax].RETAILKIT RK ON RK.RECID = RKC.KIT
INNER JOIN [ax].UNITOFMEASURE UOM ON UOM.RECID = RKC.UNITOFMEASURE
INNER JOIN [ax].RETAILCHANNELTABLE RCT ON RCT.RECID = @bi_ChannelId
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = StandaloneAndVariantIds.RECID AND IT.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
UNION
SELECT DISTINCT
RKCS.SUBSTITUTEPRODUCT AS KITLINEPRODUCTLISTING,
RKCS.QUANTITY AS QUANTITY,
UOM.SYMBOL AS UNIT,
IFNULL(RKRSC.SUBSTITUTECHARGE, 0.0) AS CHARGE,
StandaloneAndVariantIds.RECID AS KITLINEPRODUCTMASTERLISTING,
0 AS ISDEFAULTCOMPONENT,
RK.PRODUCTMASTER AS KITPRODUCTMASTERLISTING,
RKC.RECID AS KITLINEIDENTIFIER,
@bi_ChannelId AS CHANNEL,
IT.ITEMID AS ITEMID
FROM {1} StandaloneAndVariantIds -- get standalone and variants
INNER JOIN [ax].RETAILKITCOMPONENTSUBSTITUTE RKCS ON RKCS.SUBSTITUTEPRODUCT = StandaloneAndVariantIds.RECID
INNER JOIN [ax].RETAILKITCOMPONENT RKC ON RKC.RECID = RKCS.KITCOMPONENT
INNER JOIN [ax].RETAILKIT RK ON RK.RECID = RKC.KIT
INNER JOIN [ax].UNITOFMEASURE UOM ON UOM.RECID = RKCS.UNITOFMEASURE
INNER JOIN [ax].RETAILCHANNELTABLE RCT ON RCT.RECID = @bi_ChannelId
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = StandaloneAndVariantIds.RECID AND IT.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
LEFT JOIN [ax].RETAILKITRELEASEDSUBSTITUTECHARGE RKRSC ON RKRSC.KITCOMPONENTSUBSTITUTE = RKCS.RECID AND RKRSC.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
UNION
-- Variant productS
SELECT DISTINCT
RKC.COMPONENT AS KITLINEPRODUCTLISTING,
RKC.QUANTITY AS QUANTITY,
UOM.SYMBOL AS UNIT,
0 AS CHARGE,
PV.PRODUCTMASTER AS KITLINEPRODUCTMASTERLISTING,
1 AS ISDEFAULTCOMPONENT,
RK.PRODUCTMASTER AS KITPRODUCTMASTERLISTING,
RKC.RECID AS KITLINEIDENTIFIER,
@bi_ChannelId AS CHANNEL,
IT.ITEMID AS ITEMID
FROM {1} StandaloneAndVariantIds -- get standalone and variants
INNER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT PV ON PV.RECID = StandaloneAndVariantIds.RECID
INNER JOIN [ax].RETAILKITCOMPONENT RKC ON RKC.COMPONENT = StandaloneAndVariantIds.RECID
INNER JOIN [ax].RETAILKIT RK ON RK.RECID = RKC.KIT
INNER JOIN [ax].UNITOFMEASURE UOM ON UOM.RECID = RKC.UNITOFMEASURE
INNER JOIN [ax].RETAILCHANNELTABLE RCT ON RCT.RECID = @bi_ChannelId
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = PV.PRODUCTMASTER AND IT.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
UNION
SELECT DISTINCT
RKCS.SUBSTITUTEPRODUCT AS KITLINEPRODUCTLISTING,
RKCS.QUANTITY AS QUANTITY,
UOM.SYMBOL AS UNIT,
IFNULL(RKRSC.SUBSTITUTECHARGE, 0.0) AS CHARGE,
PV.PRODUCTMASTER AS KITLINEPRODUCTMASTERLISTING,
0 AS ISDEFAULTCOMPONENT,
RK.PRODUCTMASTER AS KITPRODUCTMASTERLISTING,
RKC.RECID AS KITLINEIDENTIFIER,
@bi_ChannelId AS CHANNEL,
IT.ITEMID AS ITEMID
FROM {1} StandaloneAndVariantIds -- get standalone and variants
INNER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT PV ON PV.RECID = StandaloneAndVariantIds.RECID
INNER JOIN [ax].RETAILKITCOMPONENTSUBSTITUTE RKCS ON RKCS.SUBSTITUTEPRODUCT = StandaloneAndVariantIds.RECID
INNER JOIN [ax].RETAILKITCOMPONENT RKC ON RKC.RECID = RKCS.KITCOMPONENT
INNER JOIN [ax].RETAILKIT RK ON RK.RECID = RKC.KIT
INNER JOIN [ax].UNITOFMEASURE UOM ON UOM.RECID = RKCS.UNITOFMEASURE
INNER JOIN [ax].RETAILCHANNELTABLE RCT ON RCT.RECID = @bi_ChannelId
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = StandaloneAndVariantIds.RECID AND IT.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
LEFT JOIN [ax].RETAILKITRELEASEDSUBSTITUTECHARGE RKRSC ON RKRSC.KITCOMPONENTSUBSTITUTE = RKCS.RECID AND RKRSC.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
),
KitComponentInfo AS
(
-- For the same product in a component line of a given kit, component information specified at the non-product-master level takes precedence over component information specified at the product master level.
-- Hence, add an entry from the exploded table only if an entry does not already exist in the non-product master table.
SELECT
KCVI.KITLINEPRODUCTLISTING AS KITLINEPRODUCTLISTING,
KCVI.QUANTITY AS QUANTITY,
KCVI.UNIT AS UNIT,
KCVI.CHARGE AS CHARGE,
KCVI.KITLINEPRODUCTMASTERLISTING AS KITLINEPRODUCTMASTERLISTING,
KCVI.ISDEFAULTCOMPONENT AS ISDEFAULTCOMPONENT,
KCVI.KITPRODUCTMASTERLISTING AS KITPRODUCTMASTERLISTING,
KCVI.KITLINEIDENTIFIER AS KITLINEIDENTIFIER,
KCVI.CHANNEL AS CHANNEL,
KCVI.ITEMID AS ITEMID
FROM KitComponentVariantsInfo KCVI
UNION ALL
SELECT
EKCV.KITLINEPRODUCTLISTING AS KITLINEPRODUCTLISTING,
EKCV.QUANTITY AS QUANTITY,
EKCV.UNIT AS UNIT,
EKCV.CHARGE AS CHARGE,
EKCV.KITLINEPRODUCTMASTERLISTING AS KITLINEPRODUCTMASTERLISTING,
EKCV.ISDEFAULTCOMPONENT AS ISDEFAULTCOMPONENT,
EKCV.KITPRODUCTMASTERLISTING AS KITPRODUCTMASTERLISTING,
EKCV.KITLINEIDENTIFIER AS KITLINEIDENTIFIER,
EKCV.CHANNEL AS CHANNEL,
EKCV.ITEMID AS ITEMID
FROM ExplodedKitComponentVariants EKCV
WHERE NOT EXISTS
(
SELECT
*
FROM KitComponentVariantsInfo KCVI
WHERE
KCVI.KITLINEPRODUCTLISTING = EKCV.KITLINEPRODUCTLISTING
AND KCVI.KITPRODUCTMASTERLISTING = EKCV.KITPRODUCTMASTERLISTING
AND KCVI.KITLINEIDENTIFIER = EKCV.KITLINEIDENTIFIER
)
)
SELECT
KCI.KITLINEPRODUCTLISTING AS KITLINEPRODUCTLISTING,
KCI.QUANTITY AS QUANTITY,
KCI.UNIT AS UNIT,
KCI.CHARGE AS CHARGE,
KCI.KITLINEPRODUCTMASTERLISTING AS KITLINEPRODUCTMASTERLISTING,
KCI.ISDEFAULTCOMPONENT AS ISDEFAULTCOMPONENT,
KCI.KITPRODUCTMASTERLISTING AS KITPRODUCTMASTERLISTING,
KCI.KITLINEIDENTIFIER AS KITLINEIDENTIFIER,
KCI.CHANNEL AS CHANNEL,
KCI.ITEMID AS ITEMID
FROM KitComponentInfo KCI";
PagedResult <KitComponent> kitComponents;
IEnumerable <long> standaloneAndVariantProductIds = this.request.ProductIds.Except(this.request.MasterProductIds);
IEnumerable <long> masterProductIds = this.request.MasterProductIds;
using (SqliteDatabaseContext context = new SqliteDatabaseContext(this.request.RequestContext))
using (RecordIdTableType standaloneAndVariantProductIdsTableType = new RecordIdTableType(StandaloneAndVariantProductRecordIdTableName, standaloneAndVariantProductIds))
using (RecordIdTableType masterProductIdsTableType = new RecordIdTableType(MasterProductRecordIdTableName, masterProductIds))
using (TempTable standaloneAndVariantProductIdsTempTable = context.CreateTemporaryTable(standaloneAndVariantProductIdsTableType.DataTable))
using (TempTable masterProductIdsTempTable = context.CreateTemporaryTable(masterProductIdsTableType.DataTable))
{
SqlQuery query = new SqlQuery(
GetKitComponentsQueryString,
masterProductIdsTempTable.TableName,
standaloneAndVariantProductIdsTempTable.TableName);
query.Parameters["@bi_ChannelId"] = context.ChannelId;
kitComponents = context.ReadEntity <KitComponent>(query);
}
return(kitComponents);
}
/// <summary>
/// Executes the stored procedure.
/// </summary>
/// <returns>The data response.</returns>
public PagedResult <Product> Execute()
{
if (!this.request.ProductIds.Any())
{
return(new PagedResult <Product>(new List <Product>().AsReadOnly()));
}
using (var databaseContext = new SqliteDatabaseContext(this.request.RequestContext))
using (RecordIdTableType type = new RecordIdTableType(this.request.ProductIds, ProductIdsVariableName))
{
string query = @"
-- Retrieve variant id = 0 products.
SELECT DISTINCT
[itm].UNITID AS DEFAULTUNITOFMEASURE,
[erpt].[DESCRIPTION] AS [DESCRIPTION],
[it].ITEMID AS ITEMID,
[erpt].NAME AS NAME,
CASE
WHEN [pv].RECID IS NOT NULL THEN [par].VARIANTID
ELSE [par].PRODUCTID
END AS RECID,
CASE
WHEN [par].VARIANTID = 0 AND [rk].RECID IS NOT NULL THEN 1 -- Kit Master
WHEN [rk].RECID IS NOT NULL AND [pv].RECID IS NOT NULL THEN 2 -- Kit Variant
WHEN [pv].RECID IS NULL AND [pv2].RECID IS NOT NULL THEN 3 -- Master
WHEN [pv].RECID IS NOT NULL THEN 5 -- Variant
ELSE 4 -- Standalone
END AS PRODUCTTYPE
FROM @tvp_ProductIds ids
INNER JOIN [crt].PRODUCTASSORTMENTRULES par ON [par].CHANNELID = @bi_ChannelId AND [par].PRODUCTID = [ids].RECID AND [par].VARIANTID = 0 AND [par].ISREMOTE = @i_IsRemote AND @dt_ChannelDate BETWEEN [par].VALIDFROM AND [par].VALIDTO
INNER JOIN [ax].INVENTTABLE it ON [it].PRODUCT = [par].PRODUCTID
INNER JOIN [ax].INVENTTABLEMODULE itm ON [itm].ITEMID = [it].ITEMID AND [itm].DATAAREAID = [it].DATAAREAID AND [itm].MODULETYPE = 2 -- Sales
INNER JOIN [ax].ECORESPRODUCTTRANSLATION erpt ON [erpt].PRODUCT = [par].PRODUCTID AND [erpt].LANGUAGEID = @nvc_Locale
LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT pv ON [pv].RECID = [par].VARIANTID
LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT pv2 ON [pv2].PRODUCTMASTER = [par].PRODUCTID
LEFT OUTER JOIN [ax].RETAILKIT rk ON [rk].PRODUCTMASTER = [par].PRODUCTID
UNION ALL
-- Retrieve variant id != 0 products.
SELECT DISTINCT
[itm].UNITID AS DEFAULTUNITOFMEASURE,
[erpt].[DESCRIPTION] AS [DESCRIPTION],
[it].ITEMID AS ITEMID,
[erpt].NAME AS NAME,
CASE
WHEN [pv].RECID IS NOT NULL THEN [par].VARIANTID
ELSE [par].PRODUCTID
END AS RECID,
CASE
WHEN [par].VARIANTID = 0 AND [rk].RECID IS NOT NULL THEN 1 -- Kit Master
WHEN [rk].RECID IS NOT NULL AND [pv].RECID IS NOT NULL THEN 2 -- Kit Variant
WHEN [pv].RECID IS NULL AND [pv2].RECID IS NOT NULL THEN 3 -- Master
WHEN [pv].RECID IS NOT NULL THEN 5 -- Variant
ELSE 4 -- Standalone
END AS PRODUCTTYPE
FROM @tvp_ProductIds ids
INNER JOIN [crt].PRODUCTASSORTMENTRULES par ON [par].CHANNELID = @bi_ChannelId AND [par].VARIANTID = [ids].RECID AND [par].ISREMOTE = @i_IsRemote AND @dt_ChannelDate BETWEEN [par].VALIDFROM AND [par].VALIDTO
INNER JOIN [ax].INVENTTABLE it ON [it].PRODUCT = [par].PRODUCTID
INNER JOIN [ax].INVENTTABLEMODULE itm ON [itm].ITEMID = [it].ITEMID AND [itm].DATAAREAID = [it].DATAAREAID AND [itm].MODULETYPE = 2 -- Sales
INNER JOIN [ax].ECORESPRODUCTTRANSLATION erpt ON [erpt].PRODUCT = [par].PRODUCTID AND [erpt].LANGUAGEID = @nvc_Locale
LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT pv ON [pv].RECID = [par].VARIANTID
LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT pv2 ON [pv2].PRODUCTMASTER = [par].PRODUCTID
LEFT OUTER JOIN [ax].RETAILKIT rk ON [rk].PRODUCTMASTER = [par].PRODUCTID";
long currentChannelId = this.request.RequestContext.GetPrincipal().ChannelId;
SqlQuery sqlQuery = new SqlQuery(query, type.DataTable);
sqlQuery.Parameters[DatabaseAccessor.ChannelIdVariableName] = currentChannelId;
sqlQuery.Parameters[DatabaseAccessor.ChannelDateVariableName] = this.request.RequestContext.GetNowInChannelTimeZone().DateTime;
sqlQuery.Parameters[LocaleVariableName] = this.request.RequestContext.LanguageId;
sqlQuery.Parameters[ProductIdsVariableName] = type.DataTable;
if (this.request.DownloadedProductsFilter.HasValue)
{
if (this.request.DownloadedProductsFilter.Value)
{
sqlQuery.Parameters[IsRemoteVariableName] = 1;
}
else
{
sqlQuery.Parameters[IsRemoteVariableName] = 0;
}
}
return(databaseContext.ReadEntity <Product>(sqlQuery));
}
}
