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