/// <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);
}
/// <summary>
/// Gets a temporary table containing the assorted products identifiers based on the input collection of <see name="productIds"/>.
/// </summary>
/// <returns>A temporary table containing the assorted products identifiers based on the input collection of <see name="productIds"/>.</returns>
public TempTable GetAssortedProducts()
{
string assortmentQueryText = @"
WITH AssortedProductsToChannel as
(
SELECT ral.PRODUCTID, ral.VARIANTID, ral.LINETYPE, ral.VALIDFROM, ral.VALIDTO, rct.INVENTLOCATION
FROM [ax].RETAILASSORTMENTLOOKUP ral
INNER JOIN [ax].RETAILASSORTMENTLOOKUPCHANNELGROUP ralcg ON ralcg.ASSORTMENTID = ral.ASSORTMENTID
INNER JOIN [ax].RETAILCHANNELTABLE rct ON rct.OMOPERATINGUNITID = ralcg.OMOPERATINGUNITID
WHERE
rct.RECID = @CHANNEL
AND @DATE BETWEEN ral.VALIDFROM AND ral.VALIDTO
)
INSERT INTO {0} (PRODUCTID, VARIANTID, LOOKUPID, ISMASTER, ITEMID)
SELECT
PRODUCTID,
VARIANTID,
LOOKUPID,
0 AS ISMASTER,
it.ITEMID
FROM {1} IDS
INNER JOIN [ax].INVENTTABLE it ON it.PRODUCT = IDS.LOOKUPID AND it.DATAAREAID = @DATAAREAID -- Include only Released items
WHERE
EXISTS
(
SELECT * FROM AssortedProductsToChannel AP
WHERE
AP.LINETYPE = 1
AND AP.PRODUCTID = IDS.LOOKUPID
AND (AP.VARIANTID = 0 OR AP.VARIANTID = IDS.VARIANTID)
)
AND NOT EXISTS
(
SELECT * FROM AssortedProductsToChannel AP
WHERE
AP.LINETYPE = 0
AND AP.PRODUCTID = IDS.LOOKUPID
AND (AP.VARIANTID = 0 OR AP.VARIANTID = IDS.VARIANTID)
);";
const string IncludeMasterQueryText = @"
-- includes a record for each master with VARIANTID = 0
INSERT INTO {0} (PRODUCTID, VARIANTID, LOOKUPID, ISMASTER, ITEMID)
SELECT
R.LOOKUPID,
0 AS VARIANTID,
R.LOOKUPID,
1 as ISMASTER,
R.ITEMID
FROM {0} R WHERE R.VARIANTID <> 0 GROUP BY LOOKUPID;";
// change temp table name depending on the paging settigns to make sure
// this code always returns the temp table with same name (TempAssortedProductsTableName)
string assortedProductsTableName = this.pageSettings.NoPageSizeLimit
? this.TempAssortedProductsTableName
: this.UnpagedTempAssortedProductsTableName;
// Create temp table to hold assorted products
ProductLookupTableType assortedProductsTableDefinition = new ProductLookupTableType(assortedProductsTableName);
TempTable assortedProducts = this.context.CreateTemporaryTable(assortedProductsTableDefinition.DataTable);
using (TempTable productRecidTable = this.PrepareInput())
{
SqlQuery assortmentQuery = new SqlQuery(assortmentQueryText, assortedProducts.TableName, productRecidTable.TableName);
assortmentQuery.Parameters["@CHANNEL"] = this.channelId;
assortmentQuery.Parameters["@DATE"] = this.context.ChannelDateTimeNow;
assortmentQuery.Parameters["@DATAAREAID"] = this.context.DataAreaId;
this.context.ExecuteNonQuery(assortmentQuery);
if (!this.pageSettings.NoPageSizeLimit)
{
using (TempTable unpagedResultSet = assortedProducts)
{
// page the result set
assortedProducts = this.PageResults(this.context, unpagedResultSet, this.pageSettings);
}
}
SqlQuery includeMaterProductQuery = new SqlQuery(IncludeMasterQueryText, assortedProducts.TableName);
this.context.ExecuteNonQuery(includeMaterProductQuery);
}
return(assortedProducts);
}
