CREATE FUNCTION [dbo].[giga_splitstring_to_table]
(
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
)
RETURNS @output TABLE(
    data NVARCHAR(MAX)
)
BEGIN
    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

    WHILE @start < LEN(@string) + 1 BEGIN
        IF @end = 0 
            SET @end = LEN(@string) + 1

        INSERT INTO @output (data) 
        VALUES(SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
    END
    RETURN
END
GO



CREATE FUNCTION [dbo].[giga_getnotnullnotempty]
(
    @p1 nvarchar(max) = null, 
    @p2 nvarchar(max) = null
)
RETURNS nvarchar(max)
AS
BEGIN
    IF @p1 IS NULL
        return @p2
    IF @p1 =''
        return @p2

    return @p1
END
GO



CREATE FUNCTION [dbo].[giga_getprimarykey_indexname]
(
    @table_name nvarchar(1000) = null
)
RETURNS nvarchar(1000)
AS
BEGIN
  DECLARE @index_name nvarchar(1000)

    SELECT @index_name = i.name
  FROM sys.tables AS tbl
  INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
  WHERE (i.is_unique=1 and i.is_disabled=0) and (tbl.name=@table_name)

    RETURN @index_name
END
GO


CREATE FUNCTION [dbo].[giga_padright]
(
    @source INT, 
    @symbol NVARCHAR(MAX), 
    @length INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN RIGHT(REPLICATE(@symbol, @length)+ RTRIM(CAST(@source AS NVARCHAR(MAX))), @length)
END
GO


CREATE PROCEDURE [dbo].[ProductLoadAllPaged]
(
  @CategoryIds    nvarchar(MAX) = null,  --a list of category IDs (comma-separated list). e.g. 1,2,3
  @ManufacturerId    int = 0,
  @StoreId      int = 0,
  @VendorId      int = 0,
  @WarehouseId    int = 0,
  @ProductTypeId    int = null, --product type identifier, null - load all products
  @VisibleIndividuallyOnly bit = 0,   --0 - load all products , 1 - "visible indivially" only
  @MarkedAsNewOnly  bit = 0,   --0 - load all products , 1 - "marked as new" only
  @ProductTagId    int = 0,
  @FeaturedProducts  bit = null,  --0 featured only , 1 not featured only, null - load all products
  @PriceMin      decimal(18, 4) = null,
  @PriceMax      decimal(18, 4) = null,
  @Keywords      nvarchar(4000) = null,
  @SearchDescriptions bit = 0, --a value indicating whether to search by a specified "keyword" in product descriptions
  @SearchManufacturerPartNumber bit = 0, -- a value indicating whether to search by a specified "keyword" in manufacturer part number
  @SearchSku      bit = 0, --a value indicating whether to search by a specified "keyword" in product SKU
  @SearchProductTags  bit = 0, --a value indicating whether to search by a specified "keyword" in product tags
  @UseFullTextSearch  bit = 0,
  @FullTextMode    int = 0, --0 - using CONTAINS with <prefix_term>, 5 - using CONTAINS and OR with <prefix_term>, 10 - using CONTAINS and AND with <prefix_term>
  @FilteredSpecs    nvarchar(MAX) = null,  --filter by specification attribute options (comma-separated list of IDs). e.g. 14,15,16
  @LanguageId      int = 0,
  @OrderBy      int = 0, --0 - position, 5 - Name: A to Z, 6 - Name: Z to A, 10 - Price: Low to High, 11 - Price: High to Low, 15 - creation date
  @AllowedCustomerRoleIds  nvarchar(MAX) = null,  --a list of customer role IDs (comma-separated list) for which a product should be shown (if a subjet to ACL)
  @PageIndex      int = 0, 
  @PageSize      int = 2147483644,
  @ShowHidden      bit = 0,
  @OverridePublished  bit = null, --null - process "Published" property according to "showHidden" parameter, true - load only "Published" products, false - load only "Unpublished" products
  @LoadFilterableSpecificationAttributeOptionIds bit = 0, --a value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)
  @FilterableSpecificationAttributeOptionIds nvarchar(MAX) = null OUTPUT, --the specification attribute option identifiers applied to loaded products (all pages). returned as a comma separated list of identifiers
  @TotalRecords    int = null OUTPUT
)
AS
BEGIN
  
  /* Products that filtered by keywords */
  CREATE TABLE #KeywordProducts
  (
    [ProductId] int NOT NULL
  )

  DECLARE
    @SearchKeywords bit,
    @OriginalKeywords nvarchar(4000),
    @sql nvarchar(max),
    @sql_orderby nvarchar(max)

  SET NOCOUNT ON
  
  --filter by keywords
  SET @Keywords = isnull(@Keywords, '')
  SET @Keywords = rtrim(ltrim(@Keywords))
  SET @OriginalKeywords = @Keywords
  IF ISNULL(@Keywords, '') != ''
  BEGIN
    SET @SearchKeywords = 1
    
    IF @UseFullTextSearch = 1
    BEGIN
      --remove wrong chars (' ")
      SET @Keywords = REPLACE(@Keywords, '''', '')
      SET @Keywords = REPLACE(@Keywords, '"', '')
      
      --full-text search
      IF @FullTextMode = 0 
      BEGIN
        --0 - using CONTAINS with <prefix_term>
        SET @Keywords = ' "' + @Keywords + '*" '
      END
      ELSE
      BEGIN
        --5 - using CONTAINS and OR with <prefix_term>
        --10 - using CONTAINS and AND with <prefix_term>

        --clean multiple spaces
        WHILE CHARINDEX('  ', @Keywords) > 0 
          SET @Keywords = REPLACE(@Keywords, '  ', ' ')

        DECLARE @concat_term nvarchar(100)        
        IF @FullTextMode = 5 --5 - using CONTAINS and OR with <prefix_term>
        BEGIN
          SET @concat_term = 'OR'
        END 
        IF @FullTextMode = 10 --10 - using CONTAINS and AND with <prefix_term>
        BEGIN
          SET @concat_term = 'AND'
        END

        --now let's build search string
        declare @fulltext_keywords nvarchar(4000)
        set @fulltext_keywords = N''
        declare @index int    
    
        set @index = CHARINDEX(' ', @Keywords, 0)

        -- if index = 0, then only one field was passed
        IF(@index = 0)
          set @fulltext_keywords = ' "' + @Keywords + '*" '
        ELSE
        BEGIN    
          DECLARE @first BIT
          SET  @first = 1      
          WHILE @index > 0
          BEGIN
            IF (@first = 0)
              SET @fulltext_keywords = @fulltext_keywords + ' ' + @concat_term + ' '
            ELSE
              SET @first = 0

            SET @fulltext_keywords = @fulltext_keywords + '"' + SUBSTRING(@Keywords, 1, @index - 1) + '*"'          
            SET @Keywords = SUBSTRING(@Keywords, @index + 1, LEN(@Keywords) - @index)            
            SET @index = CHARINDEX(' ', @Keywords, 0)
          end
          
          -- add the last field
          IF LEN(@fulltext_keywords) > 0
            SET @fulltext_keywords = @fulltext_keywords + ' ' + @concat_term + ' ' + '"' + SUBSTRING(@Keywords, 1, LEN(@Keywords)) + '*"'  
        END
        SET @Keywords = @fulltext_keywords
      END
    END
    ELSE
    BEGIN
      --usual search by PATINDEX
      SET @Keywords = '%' + @Keywords + '%'
    END
    --PRINT @Keywords

    --product name
    SET @sql = '
    INSERT INTO #KeywordProducts ([ProductId])
    SELECT p.Id
    FROM Product p with (NOLOCK)
    WHERE '
    IF @UseFullTextSearch = 1
      SET @sql = @sql + 'CONTAINS(p.[Name], @Keywords) '
    ELSE
      SET @sql = @sql + 'PATINDEX(@Keywords, p.[Name]) > 0 '


    --localized product name
    SET @sql = @sql + '
    UNION
    SELECT lp.EntityId
    FROM LocalizedProperty lp with (NOLOCK)
    WHERE
      lp.LocaleKeyGroup = N''Product''
      AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
      AND lp.LocaleKey = N''Name'''
    IF @UseFullTextSearch = 1
      SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
    ELSE
      SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
  

    IF @SearchDescriptions = 1
    BEGIN
      --product short description
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE '
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(p.[ShortDescription], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, p.[ShortDescription]) > 0 '


      --product full description
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE '
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(p.[FullDescription], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, p.[FullDescription]) > 0 '



      --localized product short description
      SET @sql = @sql + '
      UNION
      SELECT lp.EntityId
      FROM LocalizedProperty lp with (NOLOCK)
      WHERE
        lp.LocaleKeyGroup = N''Product''
        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        AND lp.LocaleKey = N''ShortDescription'''
      IF @UseFullTextSearch = 1
        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
      ELSE
        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
        

      --localized product full description
      SET @sql = @sql + '
      UNION
      SELECT lp.EntityId
      FROM LocalizedProperty lp with (NOLOCK)
      WHERE
        lp.LocaleKeyGroup = N''Product''
        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        AND lp.LocaleKey = N''FullDescription'''
      IF @UseFullTextSearch = 1
        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
      ELSE
        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
    END

    --manufacturer part number (exact match)
    IF @SearchManufacturerPartNumber = 1
    BEGIN
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE p.[ManufacturerPartNumber] = @OriginalKeywords '
    END

    --SKU (exact match)
    IF @SearchSku = 1
    BEGIN
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE p.[Sku] = @OriginalKeywords '
    END

    IF @SearchProductTags = 1
    BEGIN
      --product tags (exact match)
      SET @sql = @sql + '
      UNION
      SELECT pptm.Product_Id
      FROM Product_ProductTag_Mapping pptm with(NOLOCK) INNER JOIN ProductTag pt with(NOLOCK) ON pt.Id = pptm.ProductTag_Id
      WHERE pt.[Name] = @OriginalKeywords '

      --localized product tags
      SET @sql = @sql + '
      UNION
      SELECT pptm.Product_Id
      FROM LocalizedProperty lp with (NOLOCK) INNER JOIN Product_ProductTag_Mapping pptm with(NOLOCK) ON lp.EntityId = pptm.ProductTag_Id
      WHERE
        lp.LocaleKeyGroup = N''ProductTag''
        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        AND lp.LocaleKey = N''Name''
        AND lp.[LocaleValue] = @OriginalKeywords '
    END

    --PRINT (@sql)
    EXEC sp_executesql @sql, N'@Keywords nvarchar(4000), @OriginalKeywords nvarchar(4000)', @Keywords, @OriginalKeywords

  END
  ELSE
  BEGIN
    SET @SearchKeywords = 0
  END

  --filter by category IDs
  SET @CategoryIds = isnull(@CategoryIds, '')  
  CREATE TABLE #FilteredCategoryIds
  (
    CategoryId int not null
  )
  INSERT INTO #FilteredCategoryIds (CategoryId)
  SELECT CAST(data as int) FROM [giga_splitstring_to_table](@CategoryIds, ',')  
  DECLARE @CategoryIdsCount int  
  SET @CategoryIdsCount = (SELECT COUNT(1) FROM #FilteredCategoryIds)

  --filter by customer role IDs (access control list)
  SET @AllowedCustomerRoleIds = isnull(@AllowedCustomerRoleIds, '')  
  CREATE TABLE #FilteredCustomerRoleIds
  (
    CustomerRoleId int not null
  )
  INSERT INTO #FilteredCustomerRoleIds (CustomerRoleId)
  SELECT CAST(data as int) FROM [giga_splitstring_to_table](@AllowedCustomerRoleIds, ',')
  DECLARE @FilteredCustomerRoleIdsCount int  
  SET @FilteredCustomerRoleIdsCount = (SELECT COUNT(1) FROM #FilteredCustomerRoleIds)
  
  --paging
  DECLARE @PageLowerBound int
  DECLARE @PageUpperBound int
  DECLARE @RowsToReturn int
  SET @RowsToReturn = @PageSize * (@PageIndex + 1)  
  SET @PageLowerBound = @PageSize * @PageIndex
  SET @PageUpperBound = @PageLowerBound + @PageSize + 1
  
  CREATE TABLE #DisplayOrderTmp 
  (
    [Id] int IDENTITY (1, 1) NOT NULL,
    [ProductId] int NOT NULL
  )

  SET @sql = '
  SELECT p.Id
  FROM
    Product p with (NOLOCK)'
  
  IF @CategoryIdsCount > 0
  BEGIN
    SET @sql = @sql + '
    INNER JOIN Product_Category_Mapping pcm with (NOLOCK)
      ON p.Id = pcm.ProductId'
  END
  
  IF @ManufacturerId > 0
  BEGIN
    SET @sql = @sql + '
    INNER JOIN Product_Manufacturer_Mapping pmm with (NOLOCK)
      ON p.Id = pmm.ProductId'
  END
  
  IF ISNULL(@ProductTagId, 0) != 0
  BEGIN
    SET @sql = @sql + '
    INNER JOIN Product_ProductTag_Mapping pptm with (NOLOCK)
      ON p.Id = pptm.Product_Id'
  END
  
  --searching by keywords
  IF @SearchKeywords = 1
  BEGIN
    SET @sql = @sql + '
    JOIN #KeywordProducts kp
      ON  p.Id = kp.ProductId'
  END
  
  SET @sql = @sql + '
  WHERE
    p.Deleted = 0'
  
  --filter by category
  IF @CategoryIdsCount > 0
  BEGIN
    SET @sql = @sql + '
    AND pcm.CategoryId IN ('
    
    SET @sql = @sql + + CAST(@CategoryIds AS nvarchar(max))

    SET @sql = @sql + ')'

    IF @FeaturedProducts IS NOT NULL
    BEGIN
      SET @sql = @sql + '
    AND pcm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max))
    END
  END
  
  --filter by manufacturer
  IF @ManufacturerId > 0
  BEGIN
    SET @sql = @sql + '
    AND pmm.ManufacturerId = ' + CAST(@ManufacturerId AS nvarchar(max))
    
    IF @FeaturedProducts IS NOT NULL
    BEGIN
      SET @sql = @sql + '
    AND pmm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max))
    END
  END
  
  --filter by vendor
  IF @VendorId > 0
  BEGIN
    SET @sql = @sql + '
    AND p.VendorId = ' + CAST(@VendorId AS nvarchar(max))
  END
  
  --filter by warehouse
  IF @WarehouseId > 0
  BEGIN
    --we should also ensure that 'ManageInventoryMethodId' is set to 'ManageStock' (1)
    --but we skip it in order to prevent hard-coded values (e.g. 1) and for better performance
    SET @sql = @sql + '
    AND  
      (
        (p.UseMultipleWarehouses = 0 AND
          p.WarehouseId = ' + CAST(@WarehouseId AS nvarchar(max)) + ')
        OR
        (p.UseMultipleWarehouses > 0 AND
          EXISTS (SELECT 1 FROM ProductWarehouseInventory [pwi]
          WHERE [pwi].WarehouseId = ' + CAST(@WarehouseId AS nvarchar(max)) + ' AND [pwi].ProductId = p.Id))
      )'
  END
  
  --filter by product type
  IF @ProductTypeId is not null
  BEGIN
    SET @sql = @sql + '
    AND p.ProductTypeId = ' + CAST(@ProductTypeId AS nvarchar(max))
  END
  
  --filter by "visible individually"
  IF @VisibleIndividuallyOnly = 1
  BEGIN
    SET @sql = @sql + '
    AND p.VisibleIndividually = 1'
  END
  
  --filter by "marked as new"
  IF @MarkedAsNewOnly = 1
  BEGIN
    SET @sql = @sql + '
    AND p.MarkAsNew = 1
    AND (getutcdate() BETWEEN ISNULL(p.MarkAsNewStartDateTimeUtc, ''1/1/1900'') and ISNULL(p.MarkAsNewEndDateTimeUtc, ''1/1/2999''))'
  END
  
  --filter by product tag
  IF ISNULL(@ProductTagId, 0) != 0
  BEGIN
    SET @sql = @sql + '
    AND pptm.ProductTag_Id = ' + CAST(@ProductTagId AS nvarchar(max))
  END
  
  --"Published" property
  IF (@OverridePublished is null)
  BEGIN
    --process according to "showHidden"
    IF @ShowHidden = 0
    BEGIN
      SET @sql = @sql + '
      AND p.Published = 1'
    END
  END
  ELSE IF (@OverridePublished = 1)
  BEGIN
    --published only
    SET @sql = @sql + '
    AND p.Published = 1'
  END
  ELSE IF (@OverridePublished = 0)
  BEGIN
    --unpublished only
    SET @sql = @sql + '
    AND p.Published = 0'
  END
  
  --show hidden
  IF @ShowHidden = 0
  BEGIN
    SET @sql = @sql + '
    AND p.Deleted = 0
    AND (getutcdate() BETWEEN ISNULL(p.AvailableStartDateTimeUtc, ''1/1/1900'') and ISNULL(p.AvailableEndDateTimeUtc, ''1/1/2999''))'
  END
  
  --min price
  IF @PriceMin is not null
  BEGIN
    SET @sql = @sql + '
    AND (p.Price >= ' + CAST(@PriceMin AS nvarchar(max)) + ')'
  END
  
  --max price
  IF @PriceMax is not null
  BEGIN
    SET @sql = @sql + '
    AND (p.Price <= ' + CAST(@PriceMax AS nvarchar(max)) + ')'
  END
  
  --show hidden and ACL
  IF  @ShowHidden = 0 and @FilteredCustomerRoleIdsCount > 0
  BEGIN
    SET @sql = @sql + '
    AND (p.SubjectToAcl = 0 OR EXISTS (
      SELECT 1 FROM #FilteredCustomerRoleIds [fcr]
      WHERE
        [fcr].CustomerRoleId IN (
          SELECT [acl].CustomerRoleId
          FROM [AclRecord] acl with (NOLOCK)
          WHERE [acl].EntityId = p.Id AND [acl].EntityName = ''Product''
        )
      ))'
  END
  
  --filter by store
  IF @StoreId > 0
  BEGIN
    SET @sql = @sql + '
    AND (p.LimitedToStores = 0 OR EXISTS (
      SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
      WHERE [sm].EntityId = p.Id AND [sm].EntityName = ''Product'' and [sm].StoreId=' + CAST(@StoreId AS nvarchar(max)) + '
      ))'
  END
  
    --prepare filterable specification attribute option identifier (if requested)
    IF @LoadFilterableSpecificationAttributeOptionIds = 1
  BEGIN    
    CREATE TABLE #FilterableSpecs 
    (
      [SpecificationAttributeOptionId] int NOT NULL
    )
        DECLARE @sql_filterableSpecs nvarchar(max)
        SET @sql_filterableSpecs = '
          INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
          SELECT DISTINCT [psam].SpecificationAttributeOptionId
          FROM [Product_SpecificationAttribute_Mapping] [psam] WITH (NOLOCK)
              WHERE [psam].[AllowFiltering] = 1
              AND [psam].[ProductId] IN (' + @sql + ')'

        EXEC sp_executesql @sql_filterableSpecs

    --build comma separated list of filterable identifiers
    SELECT @FilterableSpecificationAttributeOptionIds = COALESCE(@FilterableSpecificationAttributeOptionIds + ',' , '') + CAST(SpecificationAttributeOptionId as nvarchar(4000))
    FROM #FilterableSpecs

    DROP TABLE #FilterableSpecs
   END

  --filter by specification attribution options
  SET @FilteredSpecs = isnull(@FilteredSpecs, '')  
  CREATE TABLE #FilteredSpecs
  (
    SpecificationAttributeOptionId int not null
  )
  INSERT INTO #FilteredSpecs (SpecificationAttributeOptionId)
  SELECT CAST(data as int) FROM [giga_splitstring_to_table](@FilteredSpecs, ',') 

    CREATE TABLE #FilteredSpecsWithAttributes
  (
        SpecificationAttributeId int not null,
    SpecificationAttributeOptionId int not null
  )
  INSERT INTO #FilteredSpecsWithAttributes (SpecificationAttributeId, SpecificationAttributeOptionId)
  SELECT sao.SpecificationAttributeId, fs.SpecificationAttributeOptionId
    FROM #FilteredSpecs fs INNER JOIN SpecificationAttributeOption sao ON sao.Id = fs.SpecificationAttributeOptionId
    ORDER BY sao.SpecificationAttributeId 

    DECLARE @SpecAttributesCount int  
  SET @SpecAttributesCount = (SELECT COUNT(1) FROM #FilteredSpecsWithAttributes)
  IF @SpecAttributesCount > 0
  BEGIN
    --do it for each specified specification option
    DECLARE @SpecificationAttributeOptionId int
        DECLARE @SpecificationAttributeId int
        DECLARE @LastSpecificationAttributeId int
        SET @LastSpecificationAttributeId = 0
    DECLARE cur_SpecificationAttributeOption CURSOR FOR
    SELECT SpecificationAttributeId, SpecificationAttributeOptionId
    FROM #FilteredSpecsWithAttributes

    OPEN cur_SpecificationAttributeOption
        FOREACH:
            FETCH NEXT FROM cur_SpecificationAttributeOption INTO @SpecificationAttributeId, @SpecificationAttributeOptionId
            IF (@LastSpecificationAttributeId <> 0 AND @SpecificationAttributeId <> @LastSpecificationAttributeId OR @@FETCH_STATUS <> 0) 
          SET @sql = @sql + '
        AND p.Id in (select psam.ProductId from [Product_SpecificationAttribute_Mapping] psam with (NOLOCK) where psam.AllowFiltering = 1 and psam.SpecificationAttributeOptionId IN (SELECT SpecificationAttributeOptionId FROM #FilteredSpecsWithAttributes WHERE SpecificationAttributeId = ' + CAST(@LastSpecificationAttributeId AS nvarchar(max)) + '))'
            SET @LastSpecificationAttributeId = @SpecificationAttributeId
    IF @@FETCH_STATUS = 0 GOTO FOREACH
    CLOSE cur_SpecificationAttributeOption
    DEALLOCATE cur_SpecificationAttributeOption
  END

  --sorting
  SET @sql_orderby = ''  
  IF @OrderBy = 5 /* Name: A to Z */
    SET @sql_orderby = ' p.[Name] ASC'
  ELSE IF @OrderBy = 6 /* Name: Z to A */
    SET @sql_orderby = ' p.[Name] DESC'
  ELSE IF @OrderBy = 10 /* Price: Low to High */
    SET @sql_orderby = ' p.[Price] ASC'
  ELSE IF @OrderBy = 11 /* Price: High to Low */
    SET @sql_orderby = ' p.[Price] DESC'
  ELSE IF @OrderBy = 15 /* creation date */
    SET @sql_orderby = ' p.[CreatedOnUtc] DESC'
  ELSE /* default sorting, 0 (position) */
  BEGIN
    --category position (display order)
    IF @CategoryIdsCount > 0 SET @sql_orderby = ' pcm.DisplayOrder ASC'
    
    --manufacturer position (display order)
    IF @ManufacturerId > 0
    BEGIN
      IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
      SET @sql_orderby = @sql_orderby + ' pmm.DisplayOrder ASC'
    END
    
    --name
    IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
    SET @sql_orderby = @sql_orderby + ' p.[Name] ASC'
  END
  
  SET @sql = @sql + '
  ORDER BY' + @sql_orderby
  
    SET @sql = '
    INSERT INTO #DisplayOrderTmp ([ProductId])' + @sql

  --PRINT (@sql)
  EXEC sp_executesql @sql

  DROP TABLE #FilteredCategoryIds
  DROP TABLE #FilteredSpecs
    DROP TABLE #FilteredSpecsWithAttributes
  DROP TABLE #FilteredCustomerRoleIds
  DROP TABLE #KeywordProducts

  CREATE TABLE #PageIndex 
  (
    [IndexId] int IDENTITY (1, 1) NOT NULL,
    [ProductId] int NOT NULL
  )
  INSERT INTO #PageIndex ([ProductId])
  SELECT ProductId
  FROM #DisplayOrderTmp
  GROUP BY ProductId
  ORDER BY min([Id])

  --total records
  SET @TotalRecords = @@rowcount
  
  DROP TABLE #DisplayOrderTmp

  --return products
  SELECT TOP (@RowsToReturn)
    p.*
  FROM
    #PageIndex [pi]
    INNER JOIN Product p with (NOLOCK) on p.Id = [pi].[ProductId]
  WHERE
    [pi].IndexId > @PageLowerBound AND 
    [pi].IndexId < @PageUpperBound
  ORDER BY
    [pi].IndexId
  
  DROP TABLE #PageIndex
END
GO


CREATE PROCEDURE [dbo].[ProductTagCountLoadAll]
(
  @StoreId int
)
AS
BEGIN
  SET NOCOUNT ON
  
  SELECT pt.Id as [ProductTagId], COUNT(p.Id) as [ProductCount]
  FROM ProductTag pt with (NOLOCK)
  LEFT JOIN Product_ProductTag_Mapping pptm with (NOLOCK) ON pt.[Id] = pptm.[ProductTag_Id]
  LEFT JOIN Product p with (NOLOCK) ON pptm.[Product_Id] = p.[Id]
  WHERE
    p.[Deleted] = 0
    AND p.Published = 1
    AND (@StoreId = 0 or (p.LimitedToStores = 0 OR EXISTS (
      SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
      WHERE [sm].EntityId = p.Id AND [sm].EntityName = 'Product' and [sm].StoreId=@StoreId
      )))
  GROUP BY pt.Id
  ORDER BY pt.Id
END
GO

CREATE PROCEDURE [dbo].[FullText_IsSupported]
AS
BEGIN  
  EXEC('
  SELECT CASE SERVERPROPERTY(''IsFullTextInstalled'')
  WHEN 1 THEN 
    CASE DatabaseProperty (DB_NAME(DB_ID()), ''IsFulltextEnabled'')
    WHEN 1 THEN 1
    ELSE 0
    END
  ELSE 0
  END as Value')
END
GO



CREATE PROCEDURE [dbo].[FullText_Enable]
AS
BEGIN
  --create catalog
  EXEC('
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE [name] = ''gigaCommerceFullTextCatalog'')
    CREATE FULLTEXT CATALOG [gigaCommerceFullTextCatalog] AS DEFAULT')
  
  --create indexes
  DECLARE @create_index_text nvarchar(4000)
  SET @create_index_text = '
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[Product]''))
    CREATE FULLTEXT INDEX ON [Product]([Name], [ShortDescription], [FullDescription])
    KEY INDEX [' + dbo.[giga_getprimarykey_indexname] ('Product') +  '] ON [gigaCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
  EXEC(@create_index_text)
  
  SET @create_index_text = '
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[LocalizedProperty]''))
    CREATE FULLTEXT INDEX ON [LocalizedProperty]([LocaleValue])
    KEY INDEX [' + dbo.[giga_getprimarykey_indexname] ('LocalizedProperty') +  '] ON [gigaCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
  EXEC(@create_index_text)

  SET @create_index_text = '
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[ProductTag]''))
    CREATE FULLTEXT INDEX ON [ProductTag]([Name])
    KEY INDEX [' + dbo.[giga_getprimarykey_indexname] ('ProductTag') +  '] ON [gigaCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
  EXEC(@create_index_text)
END
GO



CREATE PROCEDURE [dbo].[FullText_Disable]
AS
BEGIN
  EXEC('
  --drop indexes
  IF EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[Product]''))
    DROP FULLTEXT INDEX ON [Product]
  ')

  EXEC('
  IF EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[LocalizedProperty]''))
    DROP FULLTEXT INDEX ON [LocalizedProperty]
  ')

  EXEC('
  IF EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[ProductTag]''))
    DROP FULLTEXT INDEX ON [ProductTag]
  ')

  --drop catalog
  EXEC('
  IF EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE [name] = ''gigaCommerceFullTextCatalog'')
    DROP FULLTEXT CATALOG [gigaCommerceFullTextCatalog]
  ')
END
GO


CREATE PROCEDURE [dbo].[LanguagePackImport]
(
  @LanguageId int,
  @XmlPackage xml,
  @UpdateExistingResources bit
)
AS
BEGIN
  IF EXISTS(SELECT * FROM [Language] WHERE [Id] = @LanguageId)
  BEGIN
    CREATE TABLE #LocaleStringResourceTmp
      (
        [LanguageId] [int] NOT NULL,
        [ResourceName] [nvarchar](200) NOT NULL,
        [ResourceValue] [nvarchar](MAX) NOT NULL
      )

    INSERT INTO #LocaleStringResourceTmp (LanguageId, ResourceName, ResourceValue)
    SELECT  @LanguageId, nref.value('@Name', 'nvarchar(200)'), nref.value('Value[1]', 'nvarchar(MAX)')
    FROM  @XmlPackage.nodes('//Language/LocaleResource') AS R(nref)

    DECLARE @ResourceName nvarchar(200)
    DECLARE @ResourceValue nvarchar(MAX)
    DECLARE cur_localeresource CURSOR FOR
    SELECT LanguageId, ResourceName, ResourceValue
    FROM #LocaleStringResourceTmp
    OPEN cur_localeresource
    FETCH NEXT FROM cur_localeresource INTO @LanguageId, @ResourceName, @ResourceValue
    WHILE @@FETCH_STATUS = 0
    BEGIN
      IF (EXISTS (SELECT 1 FROM [LocaleStringResource] WHERE LanguageId=@LanguageId AND ResourceName=@ResourceName))
      BEGIN
        IF (@UpdateExistingResources = 1)
        BEGIN
          UPDATE [LocaleStringResource]
          SET [ResourceValue]=@ResourceValue
          WHERE LanguageId=@LanguageId AND ResourceName=@ResourceName
        END
      END
      ELSE 
      BEGIN
        INSERT INTO [LocaleStringResource]
        (
          [LanguageId],
          [ResourceName],
          [ResourceValue]
        )
        VALUES
        (
          @LanguageId,
          @ResourceName,
          @ResourceValue
        )
      END
      
      
      FETCH NEXT FROM cur_localeresource INTO @LanguageId, @ResourceName, @ResourceValue
      END
    CLOSE cur_localeresource
    DEALLOCATE cur_localeresource

    DROP TABLE #LocaleStringResourceTmp
  END
END
GO


CREATE PROCEDURE [dbo].[DeleteGuests]
(
  @OnlyWithoutShoppingCart bit = 1,
  @CreatedFromUtc datetime,
  @CreatedToUtc datetime,
  @TotalRecordsDeleted int = null OUTPUT
)
AS
BEGIN
  CREATE TABLE #tmp_guests (CustomerId int)
    
  INSERT #tmp_guests (CustomerId)
  SELECT [Id] FROM [Customer] c with (NOLOCK)
  WHERE
  --created from
  ((@CreatedFromUtc is null) OR (c.[CreatedOnUtc] > @CreatedFromUtc))
  AND
  --created to
  ((@CreatedToUtc is null) OR (c.[CreatedOnUtc] < @CreatedToUtc))
  AND
  --shopping cart items
  ((@OnlyWithoutShoppingCart=0) OR (NOT EXISTS(SELECT 1 FROM [ShoppingCartItem] sci with (NOLOCK) inner join [Customer] with (NOLOCK) on sci.[CustomerId]=c.[Id])))
  AND
  --guests only
  (EXISTS(SELECT 1 FROM [Customer_CustomerRole_Mapping] ccrm with (NOLOCK) inner join [Customer] with (NOLOCK) on ccrm.[Customer_Id]=c.[Id] inner join [CustomerRole] cr with (NOLOCK) on cr.[Id]=ccrm.[CustomerRole_Id] WHERE cr.[SystemName] = N'Guests'))
  AND
  --no orders
  (NOT EXISTS(SELECT 1 FROM [Order] o with (NOLOCK) inner join [Customer] with (NOLOCK) on o.[CustomerId]=c.[Id]))
  AND
  --no blog comments
  (NOT EXISTS(SELECT 1 FROM [BlogComment] bc with (NOLOCK) inner join [Customer] with (NOLOCK) on bc.[CustomerId]=c.[Id]))
  AND
  --no news comments
  (NOT EXISTS(SELECT 1 FROM [NewsComment] nc  with (NOLOCK)inner join [Customer] with (NOLOCK) on nc.[CustomerId]=c.[Id]))
  AND
  --no product reviews
  (NOT EXISTS(SELECT 1 FROM [ProductReview] pr with (NOLOCK) inner join [Customer] with (NOLOCK) on pr.[CustomerId]=c.[Id]))
  AND
  --no product reviews helpfulness
  (NOT EXISTS(SELECT 1 FROM [ProductReviewHelpfulness] prh with (NOLOCK) inner join [Customer] with (NOLOCK) on prh.[CustomerId]=c.[Id]))
  AND
  --no poll voting
  (NOT EXISTS(SELECT 1 FROM [PollVotingRecord] pvr with (NOLOCK) inner join [Customer] with (NOLOCK) on pvr.[CustomerId]=c.[Id]))
  AND
  --no forum topics 
  (NOT EXISTS(SELECT 1 FROM [Forums_Topic] ft with (NOLOCK) inner join [Customer] with (NOLOCK) on ft.[CustomerId]=c.[Id]))
  AND
  --no forum posts 
  (NOT EXISTS(SELECT 1 FROM [Forums_Post] fp with (NOLOCK) inner join [Customer] with (NOLOCK) on fp.[CustomerId]=c.[Id]))
  AND
  --no system accounts
  (c.IsSystemAccount = 0)
  
  --delete guests
  DELETE [Customer]
  WHERE [Id] IN (SELECT [CustomerId] FROM #tmp_guests)
  
  --delete attributes
  DELETE [GenericAttribute]
  WHERE ([EntityId] IN (SELECT [CustomerId] FROM #tmp_guests))
  AND
  ([KeyGroup] = N'Customer')
  
  --total records
  SELECT @TotalRecordsDeleted = COUNT(1) FROM #tmp_guests
  
  DROP TABLE #tmp_guests
END
GO


CREATE PROCEDURE [dbo].[CategoryLoadAllPaged]
(
    @ShowHidden         BIT = 0,
    @Name               NVARCHAR(MAX) = NULL,
    @StoreId            INT = 0,
    @CustomerRoleIds  NVARCHAR(MAX) = NULL,
    @PageIndex      INT = 0,
  @PageSize      INT = 2147483644,
    @TotalRecords    INT = NULL OUTPUT
)
AS
BEGIN
  SET NOCOUNT ON

    --filter by customer role IDs (access control list)
  SET @CustomerRoleIds = ISNULL(@CustomerRoleIds, '')
  CREATE TABLE #FilteredCustomerRoleIds
  (
    CustomerRoleId INT NOT NULL
  )
  INSERT INTO #FilteredCustomerRoleIds (CustomerRoleId)
  SELECT CAST(data AS INT) FROM [giga_splitstring_to_table](@CustomerRoleIds, ',')
  DECLARE @FilteredCustomerRoleIdsCount INT = (SELECT COUNT(1) FROM #FilteredCustomerRoleIds)

    --ordered categories
    CREATE TABLE #OrderedCategoryIds
  (
    [Id] int IDENTITY (1, 1) NOT NULL,
    [CategoryId] int NOT NULL
  )
    
    --get max length of DisplayOrder and Id columns (used for padding Order column)
    DECLARE @lengthId INT = (SELECT LEN(MAX(Id)) FROM [Category])
    DECLARE @lengthOrder INT = (SELECT LEN(MAX(DisplayOrder)) FROM [Category])

    --get category tree
    ;WITH [CategoryTree]
    AS (SELECT [Category].[Id] AS [Id], dbo.[giga_padright] ([Category].[DisplayOrder], '0', @lengthOrder) + '-' + dbo.[giga_padright] ([Category].[Id], '0', @lengthId) AS [Order]
        FROM [Category] WHERE [Category].[ParentCategoryId] = 0
        UNION ALL
        SELECT [Category].[Id] AS [Id], [CategoryTree].[Order] + '|' + dbo.[giga_padright] ([Category].[DisplayOrder], '0', @lengthOrder) + '-' + dbo.[giga_padright] ([Category].[Id], '0', @lengthId) AS [Order]
        FROM [Category]
        INNER JOIN [CategoryTree] ON [CategoryTree].[Id] = [Category].[ParentCategoryId])
    INSERT INTO #OrderedCategoryIds ([CategoryId])
    SELECT [Category].[Id]
    FROM [CategoryTree]
    RIGHT JOIN [Category] ON [CategoryTree].[Id] = [Category].[Id]

    --filter results
    WHERE [Category].[Deleted] = 0
    AND (@ShowHidden = 1 OR [Category].[Published] = 1)
    AND (@Name IS NULL OR @Name = '' OR [Category].[Name] LIKE ('%' + @Name + '%'))
    AND (@ShowHidden = 1 OR @FilteredCustomerRoleIdsCount  = 0 OR [Category].[SubjectToAcl] = 0
        OR EXISTS (SELECT 1 FROM #FilteredCustomerRoleIds [roles] WHERE [roles].[CustomerRoleId] IN
            (SELECT [acl].[CustomerRoleId] FROM [AclRecord] acl WITH (NOLOCK) WHERE [acl].[EntityId] = [Category].[Id] AND [acl].[EntityName] = 'Category')
        )
    )
    AND (@StoreId = 0 OR [Category].[LimitedToStores] = 0
        OR EXISTS (SELECT 1 FROM [StoreMapping] sm WITH (NOLOCK)
      WHERE [sm].[EntityId] = [Category].[Id] AND [sm].[EntityName] = 'Category' AND [sm].[StoreId] = @StoreId
    )
    )
    ORDER BY ISNULL([CategoryTree].[Order], 1)

    --total records
    SET @TotalRecords = @@ROWCOUNT

    --paging
    SELECT [Category].* FROM #OrderedCategoryIds AS [Result] INNER JOIN [Category] ON [Result].[CategoryId] = [Category].[Id]
    WHERE ([Result].[Id] > @PageSize * @PageIndex AND [Result].[Id] <= @PageSize * (@PageIndex + 1))
    ORDER BY [Result].[Id]

    DROP TABLE #FilteredCustomerRoleIds
    DROP TABLE #OrderedCategoryIds
END
GO