private ICollection <long> SearchByItemId(SqliteDatabaseContext context, IEnumerable <ProductLookupClause> itemIdLookupCollection)
{
const string Query = @"
-- Retrieve product identifiers for item identifiers.
SELECT it.PRODUCT AS RECID
FROM {0} ids
INNER JOIN [ax].INVENTTABLE it ON it.ITEMID = ids.ITEMID AND it.DATAAREAID = @nvc_DataAreaId
WHERE ids.INVENTDIMID = ''
UNION ALL
-- Retrieve variant identifiers for inventory dimensions.
SELECT idc.DISTINCTPRODUCTVARIANT AS RECID
FROM {0} ids
INNER JOIN [ax].INVENTDIMCOMBINATION idc ON idc.ITEMID = ids.ITEMID AND idc.INVENTDIMID = ids.INVENTDIMID AND idc.DATAAREAID = @nvc_DataAreaId
WHERE ids.INVENTDIMID != ''
";
using (var itemIdTable = new ItemIdSearchTableType(itemIdLookupCollection))
using (var itemIdTempTable = context.CreateTemporaryTable(itemIdTable.DataTable))
{
var sqlQuery = new SqlQuery(Query, itemIdTempTable.TableName);
sqlQuery.Parameters["@nvc_DataAreaId"] = context.DataAreaId;
return(context.ExecuteScalarCollection <long>(sqlQuery));
}
}
/// <summary>
/// Creates the temp table for customer price groups.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="customer">The customer account.</param>
/// <returns>Returns the instance of customer price group temp table.</returns>
public static TempTable CreateCustomerPriceGroups(SqliteDatabaseContext context, string customer)
{
const string CustomerPriceGroupTempTableName = "CUSTOMERPRICEGROUPS";
// Create temp table for customer price groups.
var customerPriceGroupDataTable = new DataTable(CustomerPriceGroupTempTableName);
customerPriceGroupDataTable.Columns.Add("LINEDISC", typeof(string));
customerPriceGroupDataTable.Columns.Add("MULTILINEDISC", typeof(string));
customerPriceGroupDataTable.Columns.Add("ENDDISC", typeof(string));
var customerPriceGroupTempTable = context.CreateTemporaryTable(customerPriceGroupDataTable);
const string InsertQuery = @"INSERT INTO {0} (LINEDISC, MULTILINEDISC, ENDDISC)
SELECT LINEDISC, MULTILINEDISC, ENDDISC
FROM [ax].CUSTTABLE WHERE ACCOUNTNUM = @Customer AND DATAAREAID = @nvc_DataAreaId";
var sqlQuery = new SqlQuery(InsertQuery, customerPriceGroupTempTable.TableName);
sqlQuery.Parameters["@nvc_DataAreaId"] = context.DataAreaId;
sqlQuery.Parameters["@Customer"] = customer;
context.ExecuteNonQuery(sqlQuery);
return(customerPriceGroupTempTable);
}
/// <summary>
/// Creates the temp table for multiline discount.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="itemIds">The item identifiers.</param>
/// <returns>Returns the instance of multiline discount temp table.</returns>
public static TempTable CreateMultiLineDiscount(SqliteDatabaseContext context, IEnumerable <string> itemIds)
{
const string ItemMultiLineDiscountGroups = "ItemMultilineDiscGroups";
// Create temp table for item line discount groups.
var itemMultiLineDiscountGroupsDataTable = new DataTable(ItemMultiLineDiscountGroups);
itemMultiLineDiscountGroupsDataTable.Columns.Add("MULTILINEDISC", typeof(string));
TempTable itemLineDiscountGroupsTempTable = context.CreateTemporaryTable(itemMultiLineDiscountGroupsDataTable);
using (TempTable items = TempTableHelper.CreateScalarTempTable(context, "RECID", itemIds))
{
const string InsertQuery = @"INSERT INTO {0}
(MULTILINEDISC)
SELECT DISTINCT it.MULTILINEDISC FROM [ax].[INVENTTABLEMODULE] it
INNER JOIN {1} i ON it.ITEMID = i.RECID
WHERE it.MODULETYPE = 2 AND it.DATAAREAID = @nvc_DataAreaId" ;
var sqlQuery = new SqlQuery(InsertQuery, itemLineDiscountGroupsTempTable.TableName, items.TableName);
sqlQuery.Parameters["@nvc_DataAreaId"] = context.DataAreaId;
context.ExecuteNonQuery(sqlQuery);
}
return(itemLineDiscountGroupsTempTable);
}
/// <summary>
/// Creates a temporary table that represents a collection of identifiers.
/// </summary>
/// <typeparam name="T">The type of the temp table field/ column.</typeparam>
/// <param name="context">The database context in which to create the temporary table.</param>
/// <param name="idColumnName">Identifier column name.</param>
/// <param name="ids">The collection of identifiers.</param>
/// <returns>The temporary table created.</returns>
public static TempTable CreateScalarTempTable <T>(SqliteDatabaseContext context, string idColumnName, IEnumerable <T> ids)
{
int tempTableId = context.GetNextContextIdentifier();
DataTable table = new DataTable(idColumnName + "TempTable" + tempTableId);
table.Columns.Add(idColumnName, typeof(T));
foreach (T id in ids)
{
var row = table.NewRow();
row[idColumnName] = id;
table.Rows.Add(row);
}
return(context.CreateTemporaryTable(table));
}
/// <summary>
/// Creates the temp table for account relation.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="accountRelations">The account relation identifiers.</param>
/// <returns>Returns the instance of account relation temp table.</returns>
public static TempTable CreateAccountRelation(SqliteDatabaseContext context, IEnumerable <string> accountRelations)
{
const string AccountRelationTempTable = "ACCOUNTRELATIONS";
// Create temp table for account relations.
var accountRelationsDataTable = new DataTable(AccountRelationTempTable);
accountRelationsDataTable.Columns.Add("ACCOUNTRELATION", typeof(string));
foreach (var accountRelation in accountRelations)
{
accountRelationsDataTable.Rows.Add(accountRelation);
}
return(context.CreateTemporaryTable(accountRelationsDataTable));
}
/// <summary>
/// Creates the affiliation price group temp table.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="affiliationPriceGroups">The affiliation price groups.</param>
/// <returns>Returns the affiliation price groups.</returns>
public static TempTable CreateAffiliationPriceGroup(SqliteDatabaseContext context, IEnumerable <AffiliationLoyaltyTier> affiliationPriceGroups)
{
const string AffiliationPriceGroupTempTable = "AFFILIATIONLOYALTYTIERS";
// Create temp table for price groups.
var affiliationPriceGroupDataTable = new DataTable(AffiliationPriceGroupTempTable);
affiliationPriceGroupDataTable.Columns.Add("AFFILIATIONID", typeof(long));
affiliationPriceGroupDataTable.Columns.Add("LOYALTYTIERID", typeof(long));
foreach (var pg in affiliationPriceGroups)
{
affiliationPriceGroupDataTable.Rows.Add(pg.AffiliationId, pg.LoyaltyTierId);
}
return(context.CreateTemporaryTable(affiliationPriceGroupDataTable));
}
/// <summary>
/// Creates the price group temp table.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="priceGroups">The price group identifiers.</param>
/// <returns>Returns the price group instance of the temp table.</returns>
public static TempTable CreatePriceGroup(SqliteDatabaseContext context, IEnumerable <string> priceGroups)
{
const string PriceGroupTempTable = "PRICEGROUPS";
// Create temp table for price groups.
var priceGroupDataTable = new DataTable(PriceGroupTempTable);
priceGroupDataTable.Columns.Add("RECID", typeof(long));
priceGroupDataTable.Columns.Add("PRICEGROUP", typeof(long));
priceGroupDataTable.Columns.Add("GROUPID", typeof(string));
foreach (var pg in priceGroups)
{
priceGroupDataTable.Rows.Add(0, 0, pg);
}
return(context.CreateTemporaryTable(priceGroupDataTable));
}
/// <summary>
/// Creates the temp table for item units.
/// </summary>
/// <param name="context">The database context.</param>
/// <param name="itemUnits">The item unit collection.</param>
/// <returns>Returns the temp table instance of item units.</returns>
public static TempTable CreateItemIdentifier(SqliteDatabaseContext context, IEnumerable <ItemUnit> itemUnits)
{
const string ItemIdTempTable = "ITEMIDS";
// Create temp table for item identifiers.
var itemIdDataTable = new DataTable(ItemIdTempTable);
itemIdDataTable.Columns.Add("ITEMID", typeof(string));
itemIdDataTable.Columns.Add("VARIANTINVENTDIMID", typeof(string));
itemIdDataTable.Columns.Add("PRODUCT", typeof(long));
itemIdDataTable.Columns.Add("DISTINCTPRODUCTVARIANT", typeof(long));
foreach (var item in itemUnits)
{
itemIdDataTable.Rows.Add(item.ItemId, item.VariantInventoryDimensionId, item.Product, item.DistinctProductVariant);
}
return(context.CreateTemporaryTable(itemIdDataTable));
}
/// <summary>
/// Page the results based on the paging configuration.
/// </summary>
/// <param name="dbContext">The database context.</param>
/// <param name="unpagedResultSet">The result set to be paginated.</param>
/// <param name="pagingInfo">The paging info configuration object.</param>
/// <returns>The paginated result set.</returns>
private TempTable PageResults(SqliteDatabaseContext dbContext, TempTable unpagedResultSet, PagingInfo pagingInfo)
{
// create table definition
ProductLookupTableType pagedResultTableDefinition = new ProductLookupTableType(this.TempAssortedProductsTableName);
// and temp table to hold paged results
TempTable pagedResultTempTable = dbContext.CreateTemporaryTable(pagedResultTableDefinition.DataTable);
string[] columnNames = pagedResultTableDefinition.DataTable.Columns.Select(column => column.ColumnName).ToArray();
string selectColumns = string.Join(",", columnNames);
// insert into paged result temp table, all records from the unpaged temp table based on unique lookup id
const string PaginateResultsQueryCommand = @"
INSERT INTO {1}
(
{2}
)
SELECT
{2}
FROM {0} UNPAGEDRESULT
WHERE
LOOKUPID IN
(
SELECT DISTINCT LOOKUPID
FROM {0}
ORDER BY LOOKUPID ASC
LIMIT @limitValue OFFSET @offsetValue
);";
SqlQuery query = new SqlQuery(PaginateResultsQueryCommand, unpagedResultSet.TableName, pagedResultTempTable.TableName, selectColumns);
query.Parameters["@limitValue"] = pagingInfo.Top;
query.Parameters["@offsetValue"] = pagingInfo.Skip;
// executes query
dbContext.ExecuteNonQuery(query);
return(pagedResultTempTable);
}
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 database code to get unit of measure conversions.
/// </summary>
/// <returns>The response message.</returns>
public GetUnitOfMeasureConversionDataResponse Execute()
{
PagedResult <UnitOfMeasureConversion> result = null;
const string QueryCommand = @"
SELECT
u.[ITEMID] AS ITEMID,
u.[FROMUNITID] AS FROMUNITID,
u.[TOUNITID] AS TOUNITID,
u.[FROMUOMSYMBOL] AS FROMUOMSYMBOL,
u.[TOUOMSYMBOL] AS TOUOMSYMBOL,
u.[ISBACKWARD] AS ISBACKWARD,
uomc.[RECID] AS RECID,
uomc.[DENOMINATOR] AS DENOMINATOR,
uomc.[FACTOR] AS FACTOR,
uomc.[FROMUNITOFMEASURE] AS FROMUNITOFMEASURE,
uomc.[INNEROFFSET] AS INNEROFFSET,
uomc.[NUMERATOR] AS NUMERATOR,
uomc.[OUTEROFFSET] AS OUTEROFFSET,
uomc.[PRODUCT] AS PRODUCT,
uomc.[ROUNDING] AS ROUNDING,
uomc.[TOUNITOFMEASURE] AS TOUNITOFMEASURE
FROM
(
SELECT
iuc.ITEMID,
iuc.FROMUNITID,
iuc.TOUNITID,
uom_from.SYMBOL AS FROMUOMSYMBOL,
uom_to.SYMBOL AS TOUOMSYMBOL,
CASE
WHEN uomc1.RECID IS NOT NULL THEN 0
WHEN uomc2.RECID IS NOT NULL THEN 1
WHEN uomc3.RECID IS NOT NULL THEN 0
WHEN uomc4.RECID IS NOT NULL THEN 1
END ISBACKWARD,
CASE
WHEN uomc1.RECID IS NOT NULL THEN uomc1.RECID
WHEN uomc2.RECID IS NOT NULL THEN uomc2.RECID
WHEN uomc3.RECID IS NOT NULL THEN uomc3.RECID
WHEN uomc4.RECID IS NOT NULL THEN uomc4.RECID
END RECID
FROM {0} iuc
INNER JOIN [ax].UNITOFMEASURE uom_from ON uom_from.SYMBOL = iuc.FROMUNITID
INNER JOIN [ax].UNITOFMEASURE uom_to ON uom_to.SYMBOL = iuc.TOUNITID
LEFT JOIN [ax].INVENTTABLE it ON it.ITEMID = iuc.ITEMID AND it.DATAAREAID = @nvc_DataAreaId
LEFT JOIN [ax].UNITOFMEASURECONVERSION uomc1
ON uomc1.FROMUNITOFMEASURE = uom_from.RECID
AND uomc1.TOUNITOFMEASURE = uom_to.RECID
AND uomc1.PRODUCT = it.PRODUCT
LEFT JOIN [ax].UNITOFMEASURECONVERSION uomc2
ON uomc2.FROMUNITOFMEASURE = uom_to.RECID
AND uomc2.TOUNITOFMEASURE = uom_from.RECID
AND uomc2.PRODUCT = it.PRODUCT
LEFT JOIN [ax].UNITOFMEASURECONVERSION uomc3
ON uomc3.FROMUNITOFMEASURE = uom_from.RECID
AND uomc3.TOUNITOFMEASURE = uom_to.RECID
AND uomc3.PRODUCT = 0
LEFT JOIN [ax].UNITOFMEASURECONVERSION uomc4
ON uomc4.FROMUNITOFMEASURE = uom_to.RECID
AND uomc4.TOUNITOFMEASURE = uom_from.RECID
AND uomc4.PRODUCT = 0
) U
INNER JOIN [ax].UNITOFMEASURECONVERSION uomc ON uomc.RECID = u.RECID
";
using (DataTable itemUnitConversionsTable = new DataTable("tvp_ItemUnitConversions"))
{
itemUnitConversionsTable.Columns.Add(ItemIdColumnName, typeof(string));
itemUnitConversionsTable.Columns.Add(FromUnitIdColumnName, typeof(string));
itemUnitConversionsTable.Columns.Add(ToUnitIdColumnName, typeof(string));
foreach (ItemUnitConversion itemUnitConversion in this.request.ItemUnitConversions)
{
DataRow row = itemUnitConversionsTable.NewRow();
itemUnitConversionsTable.Rows.Add(row);
row[ItemIdColumnName] = itemUnitConversion.ItemId;
row[FromUnitIdColumnName] = itemUnitConversion.FromUnitOfMeasure;
row[ToUnitIdColumnName] = itemUnitConversion.ToUnitOfMeasure;
}
using (var databaseContext = new SqliteDatabaseContext(this.request.RequestContext))
using (var itemUnitConvertionsTempTable = databaseContext.CreateTemporaryTable(itemUnitConversionsTable))
{
var sqlQuery = new SqlQuery(QueryCommand, itemUnitConvertionsTempTable.TableName);
sqlQuery.Parameters["@nvc_DataAreaId"] = databaseContext.DataAreaId;
result = databaseContext.ReadEntity <UnitOfMeasureConversion>(sqlQuery);
}
}
return(new GetUnitOfMeasureConversionDataResponse(result));
}
