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