public ReadOnlyCollection <KitDefinition> Execute()
{
using (SqliteDatabaseContext context = new SqliteDatabaseContext(this.request.RequestContext))
{
long channelId = context.ChannelId;
GetAssortedProductsProcedure assortedProductsProcedure = new GetAssortedProductsProcedure(
context,
channelId,
this.request.KitMasterProductIds,
true, // skipVariantsExpansion,
this.request.QueryResultSettings.Paging);
using (TempTable assortedProducts = assortedProductsProcedure.GetAssortedProducts())
{
const string GetKitDefinitionQueryString = @"
SELECT DISTINCT
KPM.PRODUCTID AS KITPRODUCTMASTERLISTING,
@bi_ChannelId AS CHANNEL,
RK.DISASSEMBLYATREGISTERALLOWED,
RK.RECID AS KITRECID
FROM {0} AS KPM
INNER JOIN [ax].RETAILKIT RK ON KPM.PRODUCTID = RK.PRODUCTMASTER";
SqlQuery query = new SqlQuery(GetKitDefinitionQueryString, assortedProducts.TableName);
query.Parameters["@bi_ChannelId"] = channelId;
return(context.ReadEntity <KitDefinition>(query).Results);
}
}
}
private TempTable GetAssortedProductsTable(SqliteDatabaseContext databaseContext, IEnumerable <long> productIds)
{
GetAssortedProductsProcedure assortedProductsProcedure = new GetAssortedProductsProcedure(
databaseContext,
databaseContext.ChannelId,
productIds,
expandVariants: true,
pagingSettings: this.request.QueryResultSettings.Paging);
return(assortedProductsProcedure.GetAssortedProducts());
}
private TempTable GetAssortedProductsTable(SqliteDatabaseContext context, long channelId, bool skipVariantsExpansion)
{
GetAssortedProductsProcedure assortedProductsProcedure = new GetAssortedProductsProcedure(
context,
channelId,
this.request.Criteria.Ids,
!skipVariantsExpansion,
this.request.QueryResultSettings.Paging);
return(assortedProductsProcedure.GetAssortedProducts());
}
private ReadOnlyCollection <LinkedProduct> Execute(TempTable productInputTempTable)
{
const string GetLinkedProductIdsQueryCommand = @"
SELECT LOOKUPID
FROM {0} ids
UNION
SELECT lit.PRODUCT as LOOKUPID
FROM {0} ids
INNER JOIN [ax].INVENTTABLE it ON it.PRODUCT = ids.LOOKUPID AND it.DATAAREAID = @nvc_DataAreaId
INNER JOIN [ax].RETAILINVENTLINKEDITEM rili ON rili.ITEMID = it.ITEMID and rili.DATAAREAID = it.DATAAREAID
INNER JOIN [ax].INVENTTABLE lit ON lit.ITEMID = rili.LINKEDITEMID AND lit.DATAAREAID = rili.DATAAREAID";
// get linked products ids
SqlQuery getLinkedProductIdsQuery = new SqlQuery(GetLinkedProductIdsQueryCommand, productInputTempTable.TableName);
getLinkedProductIdsQuery.Parameters["@nvc_DataAreaId"] = this.context.DataAreaId;
IEnumerable <long> linkedProductIds = this.context.ExecuteScalarCollection <long>(getLinkedProductIdsQuery);
// retrieve only assorted product ids from linked products
GetAssortedProductsProcedure getAssortedProductsProcedure = new GetAssortedProductsProcedure(
this.context,
this.channelId,
linkedProductIds,
expandVariants: false,
pagingSettings: PagingInfo.AllRecords);
const string GetLinkedProductDetailsQueryCommand = @"
SELECT DISTINCT
ap.PRODUCTID AS PRODUCT,
lit.PRODUCT AS LINKEDPRODUCT,
rili.QTY AS QTY
FROM {0} ap
INNER JOIN [ax].RETAILINVENTLINKEDITEM rili ON rili.ITEMID = ap.ITEMID AND rili.DATAAREAID = @nvc_DataAreaId
INNER JOIN [ax].INVENTTABLE lit ON lit.ITEMID = rili.LINKEDITEMID AND lit.DATAAREAID = rili.DATAAREAID";
ReadOnlyCollection <LinkedProduct> linkedProducts;
using (TempTable assortedLinkedProducts = getAssortedProductsProcedure.GetAssortedProducts())
{
SqlQuery getLinkedProductDetailsQuery = new SqlQuery(GetLinkedProductDetailsQueryCommand, assortedLinkedProducts.TableName);
getLinkedProductDetailsQuery.Parameters["@nvc_DataAreaId"] = this.context.DataAreaId;
linkedProducts = this.context.ReadEntity <LinkedProduct>(getLinkedProductDetailsQuery).Results;
}
return(linkedProducts);
}
public ReadOnlyCollection <KitConfigToComponentAssociation> Execute()
{
using (SqliteDatabaseContext context = new SqliteDatabaseContext(this.request.RequestContext))
{
long channelId = context.ChannelId;
GetAssortedProductsProcedure assortedProductsProcedure = new GetAssortedProductsProcedure(
context,
channelId,
this.request.KitMasterProductIds,
true, // skipVariantsExpansion,
PagingInfo.AllRecords);
using (TempTable assortedProducts = assortedProductsProcedure.GetAssortedProducts())
{
const string GetKitVariantMapQueryString = @"
SELECT DISTINCT
RKVC.COMPONENT AS COMPONENTPRODUCTLISTING,
RKVC.KITVARIANT AS KITPRODUCTVARIANTLISTING,
IT.PRODUCT AS KITPRODUCTMASTERLISTING,
IDC.INVENTDIMID AS INVENTDIMID,
ID.CONFIGID AS CONFIGID,
RKVC.COMPONENTRECID AS KITLINEIDENTIFIER
FROM [ax].RETAILKITVARIANTCOMPONENT RKVC
INNER JOIN [ax].INVENTDIMCOMBINATION IDC ON IDC.DISTINCTPRODUCTVARIANT = RKVC.KITVARIANT
INNER JOIN [ax].RETAILCHANNELTABLE RCT ON RCT.RECID = @bi_ChannelId
INNER JOIN [ax].INVENTDIM ID ON ID.INVENTDIMID = IDC.INVENTDIMID AND ID.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID
INNER JOIN [ax].INVENTTABLE IT ON IT.ITEMID = IDC.ITEMID
INNER JOIN {0} PID ON PID.PRODUCTID = IT.PRODUCT";
SqlQuery query = new SqlQuery(GetKitVariantMapQueryString, assortedProducts.TableName);
query.Parameters["@bi_ChannelId"] = channelId;
return(context.ReadEntity <KitConfigToComponentAssociation>(query).Results);
}
}
}
public ReadOnlyCollection <KitComponent> Execute()
{
using (SqliteDatabaseContext context = new SqliteDatabaseContext(this.request.RequestContext))
{
long channelId = context.ChannelId;
GetAssortedProductsProcedure assortedProductsProcedure = new GetAssortedProductsProcedure(
context,
channelId,
this.request.KitMasterProductIds,
true, // skipVariantsExpansion
PagingInfo.AllRecords);
using (TempTable assortedProducts = assortedProductsProcedure.GetAssortedProducts())
{
const string GetKitComponentQueryString = @"
SELECT DISTINCT
RKC.COMPONENT AS KITLINEPRODUCTLISTING,
0 AS KITLINEPRODUCTMASTERLISTING,
RKC.QUANTITY AS QUANTITY,
UOM.SYMBOL AS UNIT,
0 AS CHARGE,
1 AS ISDEFAULTCOMPONENT,
RK.PRODUCTMASTER AS KITPRODUCTMASTERLISTING,
RKC.RECID AS KITLINEIDENTIFIER,
IT.ITEMID AS ITEMID
FROM [ax].RETAILKITCOMPONENT RKC
INNER JOIN [ax].RETAILKIT RK ON RK.RECID = RKC.KIT
INNER JOIN {0} IDS ON IDS.PRODUCTID = RK.PRODUCTMASTER
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = RKC.COMPONENT
INNER JOIN [ax].UNITOFMEASURE UOM ON UOM.RECID = RKC.UNITOFMEASURE
UNION
SELECT DISTINCT
RKC.COMPONENT AS KITLINEPRODUCTLISTING,
PV.PRODUCTMASTER AS KITLINEPRODUCTMASTERLISTING,
RKC.QUANTITY AS QUANTITY,
UOM.SYMBOL AS UNIT,
0 AS CHARGE,
1 AS ISDEFAULTCOMPONENT,
RK.PRODUCTMASTER AS KITPRODUCTMASTERLISTING,
RKC.RECID AS KITLINEIDENTIFIER,
IT.ITEMID AS ITEMID
FROM [ax].RETAILKITCOMPONENT RKC
INNER JOIN [ax].RETAILKIT RK ON RK.RECID = RKC.KIT
INNER JOIN {0} IDS ON IDS.PRODUCTID = RK.PRODUCTMASTER
INNER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT PV ON PV.RECID = RKC.COMPONENT
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = PV.PRODUCTMASTER
INNER JOIN [ax].UNITOFMEASURE UOM ON UOM.RECID = RKC.UNITOFMEASURE
UNION
SELECT DISTINCT
RKCS.SUBSTITUTEPRODUCT AS KITLINEPRODUCTLISTING,
0 AS KITLINEPRODUCTMASTERLISTING,
RKCS.QUANTITY AS QUANTITY,
UOM.SYMBOL AS UNIT,
IFNULL(RKRSC.SUBSTITUTECHARGE, 0.0) AS CHARGE,
0 AS ISDEFAULTCOMPONENT,
RK.PRODUCTMASTER AS KITPRODUCTMASTERLISTING,
RKC.RECID AS KITLINEIDENTIFIER,
IT.ITEMID AS ITEMID
FROM [ax].RETAILKITCOMPONENTSUBSTITUTE RKCS
INNER JOIN [ax].RETAILKITCOMPONENT RKC ON RKC.RECID = RKCS.KITCOMPONENT
INNER JOIN [ax].RETAILKIT RK ON RK.RECID = RKC.KIT
INNER JOIN {0} IDS ON IDS.PRODUCTID = RK.PRODUCTMASTER
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = RKCS.SUBSTITUTEPRODUCT
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
UNION
SELECT DISTINCT
RKCS.SUBSTITUTEPRODUCT AS KITLINEPRODUCTLISTING,
PV.PRODUCTMASTER AS KITLINEPRODUCTMASTERLISTING,
RKCS.QUANTITY AS QUANTITY,
UOM.SYMBOL AS UNIT,
IFNULL(RKRSC.SUBSTITUTECHARGE, 0.0) AS CHARGE,
0 AS ISDEFAULTCOMPONENT,
RK.PRODUCTMASTER AS KITPRODUCTMASTERLISTING,
RKC.RECID AS KITLINEIDENTIFIER,
IT.ITEMID AS ITEMID
FROM [ax].RETAILKITCOMPONENTSUBSTITUTE RKCS
INNER JOIN [ax].RETAILKITCOMPONENT RKC ON RKC.RECID = RKCS.KITCOMPONENT
INNER JOIN [ax].RETAILKIT RK ON RK.RECID = RKC.KIT
INNER JOIN {0} IDS ON IDS.PRODUCTID = RK.PRODUCTMASTER
INNER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT PV ON PV.RECID = RKCS.SUBSTITUTEPRODUCT
INNER JOIN [ax].INVENTTABLE IT ON IT.PRODUCT = PV.PRODUCTMASTER
INNER JOIN [ax].UNITOFMEASURE UOM ON UOM.RECID = RKCS.UNITOFMEASURE
INNER JOIN [ax].RETAILCHANNELTABLE RCT ON RCT.RECID = @bi_ChannelId
LEFT JOIN [ax].RETAILKITRELEASEDSUBSTITUTECHARGE RKRSC ON RKRSC.KITCOMPONENTSUBSTITUTE = RKCS.RECID AND RKRSC.DATAAREAID = RCT.INVENTLOCATIONDATAAREAID";
SqlQuery query = new SqlQuery(GetKitComponentQueryString, assortedProducts.TableName);
query.Parameters["@bi_ChannelId"] = channelId;
return(context.ReadEntity <KitComponent>(query).Results);
}
}
}
