ALTER PROCEDURE dbo.Web_Load_product_List
(
@StartIndex INT = 1 ,
@EndIndex INT = 8 ,
@Catagory VARCHAR(MAX) = 'GOWN' ,
@Color_Id VARCHAR(50) = '' ,
@Work_Id VARCHAR(50) = '' ,
@Occassion_Id VARCHAR(50) = '' ,
@Fabric_Id VARCHAR(50) = '' ,
@Style_Id VARCHAR(500) = '' ,
@Size_ID VARCHAR(500)='',
@Sort_By VARCHAR(500)='Aprrove_Date desc',
@Min_Price DECIMAL(18,2)=0,
@Max_Price DECIMAL(18,2)=0,
@Designer_ID VARCHAR(500)='',
@Customer_Id INT =0,
@Group_Name VARCHAR(100)='',
@SubCat_Name VARCHAR(100)='',
@TotalRecords INT = 0 OUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SqlStr NVARCHAR(MAX) = '' ,@CommonStr NVARCHAR(MAX) = '',@CommonStr2 NVARCHAR(MAX) = '' ,
@total INT= 0 ;
DECLARE @Catalog_Category_ID INT = 0,@Group_ID INT=0,@SubCat_ID INT =0,@Group_On VARCHAR(20)=''
DECLARE @Search_ID VARCHAR(500)='';
SELECT TOP 1 @Catalog_Category_ID = Catalog_Category_ID FROM dbo.Catalog_Category_Master WITH(NOLOCK) WHERE Category_Name = @Catagory AND Is_Active=1
IF @Group_Name <> ''
BEGIN
SELECT TOP 1 @Group_On = Group_On,@Group_ID=Menu_Group_ID
FROM dbo.Menu_Group_Master WITH(NOLOCK)
WHERE Group_Name = @Group_Name AND Category_ID=@Catalog_Category_ID AND Menu_For='Retail'
IF @Group_On='Category'
BEGIN
SELECT TOP 1 @SubCat_ID = Catalog_Category_ID FROM dbo.Menu_ChildCategory_Master c1 WITH(NOLOCK)
LEFT JOIN dbo.Catalog_Category_Master c2 WITH(NOLOCK) ON c1.Category_ID=c2.Catalog_Category_ID
WHERE Category_Name = @SubCat_Name AND Menu_Group_ID=@Group_ID
END
ELSE IF @Group_On='Attribute'
BEGIN
SELECT TOP 1 @SubCat_ID = Designer_Id FROM dbo.Designer_Master WITH(NOLOCK)
WHERE Designer_Name = @SubCat_Name
END
END
DECLARE @where VARCHAR(MAX)= '' ,@SubCatwhere VARCHAR(MAX)= '' ,
@current INT= 1,@COUNT1 INT = 0 ;
CREATE TABLE #Temp
(
SrNo INT NULL,
Catalog_Category_ID INT NULL
);
IF @Group_On='Category'
BEGIN
IF @SubCat_ID > 0
BEGIN
WITH CTE as
(
SELECT P.Catalog_Category_ID, P.Parent_Category_ID, CAST(P.Category_Name AS VarChar(Max)) as Category_Name
, CAST(P.Parent_Category_ID AS VarChar(Max)) as Parent_Category_IDs
FROM dbo.Catalog_Category_Master P WITH(NOLOCK)
WHERE P.Catalog_Category_ID = @SubCat_ID AND Is_Active=1
UNION ALL
SELECT P1.Catalog_Category_ID, P1.Parent_Category_ID, M.Category_Name + ' >> ' + CAST(P1.Category_Name AS VarChar(Max))
, M.Parent_Category_IDs + ',' + CAST(P1.Parent_Category_ID AS VarChar(Max))
FROM Catalog_Category_Master P1 WITH(NOLOCK)
INNER JOIN CTE M
ON M.Catalog_Category_ID = P1.Parent_Category_ID
AND P1.Is_Active=1
)
INSERT INTO #Temp (SrNo,Catalog_Category_ID)
SELECT ROW_NUMBER() OVER (ORDER BY Catalog_Category_ID),Catalog_Category_ID From CTE
END
ELSE IF @Group_ID > 0
BEGIN
WITH CTE as
(
SELECT P.Group_Category_ID AS Catalog_Category_ID, c3.Parent_Category_ID, CAST(c3.Category_Name AS VarChar(Max)) as Category_Name
, CAST(c3.Parent_Category_ID AS VarChar(Max)) as Parent_Category_IDs
FROM dbo.Menu_Group_Master P WITH(NOLOCK)
LEFT JOIN dbo.Catalog_Category_Master c3 WITH(NOLOCK) ON c3.Catalog_Category_ID=P.Group_Category_ID
WHERE P.Menu_Group_ID = @Group_ID AND Is_Active=1
UNION ALL
SELECT P1.Catalog_Category_ID, P1.Parent_Category_ID, M.Category_Name + ' >> ' + CAST(P1.Category_Name AS VarChar(Max))
, M.Parent_Category_IDs + ',' + CAST(P1.Parent_Category_ID AS VarChar(Max))
FROM Catalog_Category_Master P1 WITH(NOLOCK)
INNER JOIN CTE M
ON M.Catalog_Category_ID = P1.Parent_Category_ID
AND P1.Is_Active=1
)
INSERT INTO #Temp (SrNo,Catalog_Category_ID)
SELECT ROW_NUMBER() OVER (ORDER BY Catalog_Category_ID),Catalog_Category_ID From CTE
END
SELECT @COUNT1 = COUNT(1) FROM #Temp
SET @SubCatwhere=@SubCatwhere+' AND ('
WHILE @COUNT1 >= @current
BEGIN
DECLARE @ID VARCHAR(10)= '' ;
SELECT @ID = Catalog_Category_ID
FROM #Temp
WHERE SrNo = @current ;
IF(@current>1)
BEGIN
SET @SubCatwhere=@SubCatwhere+' OR '
END
SET @SubCatwhere = @SubCatwhere + ' Category_ID='+CONVERT(VARCHAR(10), @ID) +' OR '',''+ Sub_Category_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @ID) + ',%'' ';
SET @current = @current + 1 ;
END
SET @SubCatwhere=@SubCatwhere+' ) '
END
ELSE IF @Group_On='Attribute'
BEGIN
IF @SubCat_ID > 0
BEGIN
SET @SubCatwhere=' AND cF.Promotional_Brand='+CONVERT(VARCHAR(5000), @SubCat_ID) ;
END
ELSE IF @Group_ID > 0
BEGIN
SELECT * ,
ROW_NUMBER() OVER ( ORDER BY Attribute_value ) AS SrNo
INTO #Temp1
FROM ( SELECT Attribute_value
FROM dbo.Menu_ChildCategory_Master WITH(NOLOCK)
WHERE Menu_Group_ID = @Group_ID
) AS T1
SELECT @COUNT1 = COUNT(1) FROM #Temp1
SET @current=1;
SET @SubCatwhere=@SubCatwhere+' AND ('
WHILE @COUNT1 >= @current
BEGIN
DECLARE @ID2 VARCHAR(10)= '' ;
SELECT @ID2 = Attribute_value
FROM #Temp1
WHERE SrNo = @current ;
IF(@current>1)
BEGIN
SET @SubCatwhere=@SubCatwhere+' OR '
END
SET @SubCatwhere = @SubCatwhere + ' cF.Promotional_Brand='+CONVERT(VARCHAR(10), @ID2) ;
SET @current = @current + 1 ;
END
SET @SubCatwhere=@SubCatwhere+' ) '
END
END
ELSE --if only category
BEGIN
WITH CTE as
(
SELECT P.Catalog_Category_ID, P.Parent_Category_ID, CAST(P.Category_Name AS VarChar(Max)) as Category_Name
, CAST(P.Parent_Category_ID AS VarChar(Max)) as Parent_Category_IDs
FROM dbo.Catalog_Category_Master P WITH(NOLOCK)
WHERE P.Catalog_Category_ID = @Catalog_Category_ID AND Is_Active=1
UNION ALL
SELECT P1.Catalog_Category_ID, P1.Parent_Category_ID, M.Category_Name + ' >> ' + CAST(P1.Category_Name AS VarChar(Max))
, M.Parent_Category_IDs + ',' + CAST(P1.Parent_Category_ID AS VarChar(Max))
FROM Catalog_Category_Master P1 WITH(NOLOCK)
INNER JOIN CTE M
ON M.Catalog_Category_ID = P1.Parent_Category_ID
AND P1.Is_Active=1
)
INSERT INTO #Temp (SrNo,Catalog_Category_ID)
SELECT ROW_NUMBER() OVER (ORDER BY Catalog_Category_ID),Catalog_Category_ID From CTE
SELECT @COUNT1 = COUNT(1)
FROM #Temp
SET @current=1;
SET @SubCatwhere=@SubCatwhere+' AND ('
WHILE @COUNT1 >= @current
BEGIN
DECLARE @ID1 VARCHAR(10)= '' ;
SELECT @ID1 = Catalog_Category_ID
FROM #Temp
WHERE SrNo = @current ;
IF(@current>1)
BEGIN
SET @SubCatwhere=@SubCatwhere+' OR '
END
SET @SubCatwhere = @SubCatwhere + ' Category_ID='+CONVERT(VARCHAR(10), @ID1) +' OR '',''+ Sub_Category_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @ID1) + ',%'' ';
SET @current = @current + 1 ;
END
SET @SubCatwhere=@SubCatwhere+' ) '
END
IF ( @Color_Id <> '' )
BEGIN
SELECT * INTO #TempColor FROM dbo.Split(@Color_Id,',')
SET @current=1;
SELECT @COUNT1 = COUNT(1) FROM #TempColor
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' AND ( '
END
WHILE @COUNT1 >= @current
BEGIN
IF(@current>1)
BEGIN
SET @where=@where+' OR '
END
DECLARE @ColorID VARCHAR(10)= '' ;
SELECT @ColorID = Data FROM #TempColor WHERE ID = @current ;
SET @where = @where + '
'',''+ A.Top_Color_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @ColorID) + ',%''
OR
'',''+ A.Bottom_Color_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @ColorID) + ',%''
OR
'',''+ A.Dupatta_Color_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @ColorID) + ',%''
OR
'',''+ A.Inner_Color_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @ColorID) + ',%''
' ;
SET @current = @current + 1 ;
END
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' )'
END
END
IF ( @Work_Id <> '' )
BEGIN
SELECT * INTO #TempWork FROM dbo.Split(@Work_Id,',')
SET @current=1;
SELECT @COUNT1 = COUNT(1) FROM #TempWork
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' AND ( '
END
WHILE @COUNT1 >= @current
BEGIN
DECLARE @WorkID VARCHAR(10)= '' ;
SELECT @WorkID = Data FROM #TempWork WHERE ID = @current ;
IF(@current>1)
BEGIN
SET @where=@where+' OR '
END
SET @where = @where + '
'',''+ A.Top_Work_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @WorkID) + ',%''
OR
'',''+ A.Bottom_Work_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @WorkID) + ',%''
OR
'',''+ A.Dupatta_Work_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @WorkID) + ',%''
OR
'',''+ A.Inner_Work_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @WorkID) + ',%''
' ;
SET @current = @current + 1 ;
END
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' )'
END
END
IF ( @Fabric_Id <> '' )
BEGIN
SELECT * INTO #TempFabric FROM dbo.Split(@Fabric_Id,',')
SET @current=1;
SELECT @COUNT1 = COUNT(1) FROM #TempFabric
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' AND ( '
END
WHILE @COUNT1 >= @current
BEGIN
IF(@current>1)
BEGIN
SET @where=@where+' OR '
END
DECLARE @FabricID VARCHAR(10)= '' ;
SELECT @FabricID = Data FROM #TempFabric WHERE ID = @current ;
SET @where = @where + '
'',''+ A.Top_Fabrics_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @FabricID) + ',%''
OR
'',''+ A.Bottom_Fabrics_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @FabricID) + ',%''
OR
'',''+ A.Dupatta_Fabrics_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @FabricID) + ',%''
OR
'',''+ A.Inner_Fabrics_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @FabricID) + ',%''
' ;
SET @current = @current + 1 ;
END
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' )'
END
END
IF ( @Occassion_Id <> '' )
BEGIN
Declare @TempOccIDs varchar(100)='';
SELECT @TempOccIDs= ISNULL(STUFF(( SELECT ',' + CONVERT(varchar(10),Occasion_ID) FROM dbo.Catalog_Occasion_Master WITH(NOLOCK) WHERE Occasion_Type LIKE '%'+ @Occassion_Id +'%' FOR XML PATH('') ), 1, 1, ''), '')
SELECT * INTO #TempOccasion FROM dbo.Split(@TempOccIDs,',')
SET @current=1;
SELECT @COUNT1 = COUNT(1) FROM #TempOccasion
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' AND ( '
END
WHILE @COUNT1 >= @current
BEGIN
DECLARE @OccasionID VARCHAR(10)= '' ;
SELECT @OccasionID = Data FROM #TempOccasion WHERE ID = @current ;
IF(@current>1)
BEGIN
SET @where=@where+' OR '
END
SET @where = @where + ' '',''+ A.Occasion_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @OccasionID) + ',%'' ';
SET @current = @current + 1 ;
END
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' )'
END
END
IF ( @Style_Id <> '' )
BEGIN
SELECT * INTO #TempStyle FROM dbo.Split(@Style_Id,',')
SET @current=1;
SELECT @COUNT1 = COUNT(1) FROM #TempStyle
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' AND ( '
END
WHILE @COUNT1 >= @current
BEGIN
IF(@current>1)
BEGIN
SET @where=@where+' OR '
END
DECLARE @StyleID VARCHAR(10)= '' ;
SELECT @StyleID = Data FROM #TempStyle WHERE ID = @current ;
SET @where = @where + '
'',''+ A.Sleeve_Style_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @StyleID) + ',%''
OR
'',''+ A.Neck_Style_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @StyleID) + ',%''
'
SET @current = @current + 1 ;
END
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' )'
END
END
IF ( @Size_ID <> '' )
BEGIN
SELECT * INTO #TempSize FROM dbo.Split(@Size_ID,',')
SET @current=1;
SELECT @COUNT1 = COUNT(1) FROM #TempSize
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' AND ( '
END
WHILE @COUNT1 >= @current
BEGIN
IF(@current>1)
BEGIN
SET @where=@where+' OR '
END
DECLARE @SizeID VARCHAR(10)= '' ;
SELECT @SizeID = Data FROM #TempSize WHERE ID = @current ;
SET @where = @where + ' '',''+ A.Product_Size_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @SizeID) + ',%'' ';
SET @current = @current + 1 ;
END
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' ) '
END
END
IF ( @Max_Price > 0 )
BEGIN
SET @where = @where + ' AND (
A.Sell_Single_Pcs_Rate >= ''' + CAST(@Min_Price AS VARCHAR(50)) + '''
AND A.Sell_Single_Pcs_Rate <= ''' + CAST(@Max_Price AS VARCHAR(50)) + ''')'
END
IF ( @Designer_ID <> '' )
BEGIN
SELECT * INTO #TempDesigner FROM dbo.Split(@Designer_ID,',')
SET @current=1;
SELECT @COUNT1 = COUNT(1) FROM #TempDesigner
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' AND ( '
END
WHILE @COUNT1 >= @current
BEGIN
DECLARE @DesID VARCHAR(10)= '' ;
SELECT @DesID = Data FROM #TempDesigner WHERE ID = @current ;
IF(@current>1)
BEGIN
SET @where=@where+' OR '
END
SET @where = @where + ' A.Vendor_ID =' + CONVERT(VARCHAR(10), @DesID)
SET @current = @current + 1 ;
END
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' )'
END
END
SET @CommonStr = '
SELECT ISNULL(c1.Product_ID,0) AS Product_ID ,
ISNULL(c1.Catalog_No,0 ) AS Catalog_No,
ISNULL(c1.Product_SKU ,'''') AS Product_SKU,
ISNULL(c1.Product_Name,'''') AS Product_Name ,
ISNULL(c1.Description ,'''') AS Description ,
ISNULL(c1.Vendor_SKU ,'''') AS Vendor_SKU ,
ISNULL(Lm.Designer_Id,'''') AS Vendor_ID,
ISNULL(c1.Category_ID ,'''') AS Category_ID ,
ISNULL(c1.Sub_Category_ID ,0) AS Sub_Category_ID ,
ISNULL(c1.Stock_Available_Till_Date ,'''') AS Stock_Available_Till_Date ,
ISNULL(c1.Avg_Weight_Grams ,0) AS Avg_Weight_Grams ,
ISNULL(c1.Buy_Single_Pcs_Rate ,0) AS Buy_Single_Pcs_Rate ,
ISNULL(c1.Buy_Full_Catalog_Rate ,0) AS Buy_Full_Catalog_Rate ,
ISNULL(c1.Mu_Percent ,0) AS Mu_Percent ,
ISNULL(c1.Sell_Single_Pcs_Rate ,0) AS Sell_Single_Pcs_Rate ,
ISNULL(c1.Sell_Full_Catalog_Rate ,0) AS Sell_Full_Catalog_Rate ,
ISNULL(c1.Occasion_ID ,'''') AS Occasion_ID ,
ISNULL(c1.Top_Fabrics_ID ,'''') AS Top_Fabrics_ID ,
ISNULL(c1.Bottom_Fabrics_ID ,'''') AS Bottom_Fabrics_ID ,
ISNULL(c1.Dupatta_Fabrics_ID ,'''') AS Dupatta_Fabrics_ID ,
ISNULL(c1.Inner_Fabrics_ID ,'''') AS Inner_Fabrics_ID ,
ISNULL(c1.Top_Color_ID ,'''') AS Top_Color_ID ,
ISNULL(c1.Bottom_Color_ID ,'''') AS Bottom_Color_ID ,
ISNULL(c1.Dupatta_Color_ID ,'''') AS Dupatta_Color_ID ,
ISNULL(c1.Inner_Color_ID ,'''') AS Inner_Color_ID ,
ISNULL(c1.Top_Work_ID ,'''') AS Top_Work_ID ,
ISNULL(c1.Bottom_Work_ID ,'''') AS Bottom_Work_ID ,
ISNULL(c1.Dupatta_Work_ID ,'''') AS Dupatta_Work_ID ,
ISNULL(c1.Inner_Work_ID ,'''') AS Inner_Work_ID ,
ISNULL(c1.Packing_Dimensions ,'''') AS Packing_Dimensions ,
ISNULL(c1.Sleeve_Style_ID ,'''') AS Sleeve_Style_ID ,
ISNULL(c1.Neck_Style_ID ,'''') AS Neck_Style_ID ,
ISNULL(c1.Product_Tag_ID ,'''') AS Product_Tag_ID ,
ISNULL(c1.Packing_Type_ID ,'''') AS Packing_Type_ID ,
ISNULL(c1.Stitching_ID ,0) AS Stitching_ID ,
ISNULL(c1.Stitching_Option_ID ,0) AS Stitching_Option_ID ,
ISNULL(c1.Product_For ,0) AS Product_For ,
ISNULL(c1.Product_Size_ID ,'''') AS Product_Size_ID ,
ISNULL(c1.Skin_Type_ID ,'''') AS Skin_Type_ID ,
ISNULL(c1.Item_Form_ID ,'''') AS Item_Form_ID ,
ISNULL(c1.Target_Gender ,'''') AS Target_Gender ,
ISNULL(c1.Minimum_Reorder_Level ,0) AS Minimum_Reorder_Level ,
ISNULL(c1.Detail_Allow_Online_Display ,'''') AS Detail_Allow_Online_Display ,
ISNULL(c1.Seo_Meta_Tag ,'''') AS Seo_Meta_Tag ,
ISNULL(c1.Seo_Title ,'''') AS Seo_Title ,
ISNULL(c1.Seo_FocusKeyword ,'''') AS Seo_FocusKeyword ,
ISNULL(c1.Seo_Meta_Description ,'''') AS Seo_Meta_Description ,
ISNULL(c1.Original_Image_Path ,'''') AS Original_Image_Path ,
Lm.Designer_Name AS Ledger_Name,
ISNULL(CONVERT(DECIMAL(15,2),
CONVERT(DECIMAL(15,0),(SELECT SUM(ISNULL(RM.Rating,0)) FROM dbo.Review_Master RM WITH(NOLOCK) WHERE RM.Product_Id= c1.Product_ID))/
nullif(CONVERT(DECIMAL(15,0),(SELECT COUNT(1) FROM dbo.Review_Master RM WITH(NOLOCK) WHERE RM.Product_Id=c1.Product_ID)),0)),0) AS Average_Rating
,CONVERT(DECIMAL(15,0),(SELECT COUNT(1) FROM dbo.Review_Master RM WITH(NOLOCK) WHERE RM.Product_Id=c1.Product_ID),0) AS Total_Review,
--(SELECT Count(*) FROM Product_Like_Master X WHERE X.Product_ID = c1.Product_ID AND Customer_ID= '+CAST(@Customer_Id AS VARCHAR(max))+ ' AND Status=1 AND Count > 0) AS AlreadyWishListed ,
(SELECT Count(1) FROM UserWishlist_Details X WITH(NOLOCK) WHERE X.Product_ID = c1.Product_ID AND Customer_ID= '+CAST(@Customer_Id AS VARCHAR(max))+ ' AND X.Is_Wholesale=0) AS AlreadyWishListed ,
ISNULL((SELECT TOP 1 Product_Like_ID FROM Product_Like_Master X WITH(NOLOCK) WHERE X.Product_ID = c1.Product_ID AND Customer_ID='+CAST(@Customer_Id AS VARCHAR(max))+ ' AND Status = 1 AND Count > 0),0) AS Product_Like_ID,
ISNULL((SELECT SUM(Count) FROM Product_Like_Master X WITH(NOLOCK) WHERE X.Product_ID = c1.Product_ID ),0) AS TotalLike,
c1.IsActive,
ISNULL(Image_Alter, '''') Image_Alter ,
ISNULL(Image_Meta_Title, '''') Image_Meta_Title ,
ISNULL(Image_Meta_Description, '''') Image_Meta_Description ,
ISNULL(Product_Meta_Title, '''') Product_Meta_Title ,
ISNULL(Product_Meta_Description, '''') Product_Meta_Description ,
ISNULL(Product_Detail_Meta_Title, '''') Product_Detail_Meta_Title ,
ISNULL(Product_Detail_Meta_Description, '''') Product_Detail_Meta_Description,cf.Aprrove_Date,
(SELECT COUNT(1) FROM ( SELECT om.Offer_ID FROM dbo.Offer_Master om WITH(NOLOCK) WHERE om.Is_Active = 1 AND ( CAST(GETDATE() AS DATE) BETWEEN om.From_Date AND om.To_Date )
AND om.Effect_Allow_From_Applicable <=cast (GETDATE() as DATE) AND Effect_Product_Wise = 0 UNION ALL
SELECT om.Offer_ID FROM dbo.Offer_Master om WITH(NOLOCK) LEFT JOIN dbo.Offer_Detail od WITH ( NOLOCK ) ON od.Offer_ID = om.Offer_ID
WHERE om.Is_Active = 1 AND '','' + Product_ID + '','' LIKE ''%,'' + CONVERT(VARCHAR(10), c1.Product_ID) + '',%''
AND ( CAST(GETDATE() AS DATE) BETWEEN om.From_Date AND om.To_Date ) AND om.Effect_Allow_From_Applicable <=cast (GETDATE() as DATE) ) AS T1)Discount
FROM dbo.Catalog_Product_Detail c1 WITH(NOLOCK)
--INNER JOIN #Temp C3 WITH(NOLOCK) ON C3.Catalog_Category_ID =c1.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=c1.Catalog_No
LEFT JOIN dbo.Designer_Master Lm WITH(NOLOCK) ON LM.Designer_Id=CAST(cF.Promotional_Brand AS INT)
WHERE IsActive=1 AND c1.Detail_Allow_Online_Display=1 AND cF.IsApprove=1 AND ( lm.Is_Active = 1 OR lm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
AND c1.Sell_Single_Pcs_Rate > 0 AND c1.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND c1.Product_For != 2' + @SubCatwhere
--set @CommonStr2 = '
-- UNION
-- SELECT ISNULL(c1.Product_ID,0) AS Product_ID ,
-- ISNULL(c1.Catalog_No,0 ) AS Catalog_No,
-- ISNULL(c1.Product_SKU ,'''') AS Product_SKU,
-- ISNULL(c1.Product_Name,'''') AS Product_Name ,
-- ISNULL(c1.Description ,'''') AS Description ,
-- ISNULL(c1.Vendor_SKU ,'''') AS Vendor_SKU ,
-- ISNULL(Lm.Designer_Id,'''') AS Vendor_ID,
-- ISNULL(c1.Category_ID ,'''') AS Category_ID ,
-- ISNULL(c1.Sub_Category_ID ,0) AS Sub_Category_ID ,
-- ISNULL(c1.Stock_Available_Till_Date ,'''') AS Stock_Available_Till_Date ,
-- ISNULL(c1.Avg_Weight_Grams ,0) AS Avg_Weight_Grams ,
-- ISNULL(c1.Buy_Single_Pcs_Rate ,0) AS Buy_Single_Pcs_Rate ,
-- ISNULL(c1.Buy_Full_Catalog_Rate ,0) AS Buy_Full_Catalog_Rate ,
-- ISNULL(c1.Mu_Percent ,0) AS Mu_Percent ,
-- ISNULL(c1.Sell_Single_Pcs_Rate ,0) AS Sell_Single_Pcs_Rate ,
-- ISNULL(c1.Sell_Full_Catalog_Rate ,0) AS Sell_Full_Catalog_Rate ,
-- ISNULL(c1.Occasion_ID ,'''') AS Occasion_ID ,
-- ISNULL(c1.Top_Fabrics_ID ,'''') AS Top_Fabrics_ID ,
-- ISNULL(c1.Bottom_Fabrics_ID ,'''') AS Bottom_Fabrics_ID ,
-- ISNULL(c1.Dupatta_Fabrics_ID ,'''') AS Dupatta_Fabrics_ID ,
-- ISNULL(c1.Inner_Fabrics_ID ,'''') AS Inner_Fabrics_ID ,
-- ISNULL(c1.Top_Color_ID ,'''') AS Top_Color_ID ,
-- ISNULL(c1.Bottom_Color_ID ,'''') AS Bottom_Color_ID ,
-- ISNULL(c1.Dupatta_Color_ID ,'''') AS Dupatta_Color_ID ,
-- ISNULL(c1.Inner_Color_ID ,'''') AS Inner_Color_ID ,
-- ISNULL(c1.Top_Work_ID ,'''') AS Top_Work_ID ,
-- ISNULL(c1.Bottom_Work_ID ,'''') AS Bottom_Work_ID ,
-- ISNULL(c1.Dupatta_Work_ID ,'''') AS Dupatta_Work_ID ,
-- ISNULL(c1.Inner_Work_ID ,'''') AS Inner_Work_ID ,
-- ISNULL(c1.Packing_Dimensions ,'''') AS Packing_Dimensions ,
-- ISNULL(c1.Sleeve_Style_ID ,'''') AS Sleeve_Style_ID ,
-- ISNULL(c1.Neck_Style_ID ,'''') AS Neck_Style_ID ,
-- ISNULL(c1.Product_Tag_ID ,'''') AS Product_Tag_ID ,
-- ISNULL(c1.Packing_Type_ID ,'''') AS Packing_Type_ID ,
-- ISNULL(c1.Stitching_ID ,0) AS Stitching_ID ,
-- ISNULL(c1.Stitching_Option_ID ,0) AS Stitching_Option_ID ,
-- ISNULL(c1.Product_For ,0) AS Product_For ,
-- ISNULL(c1.Product_Size_ID ,'''') AS Product_Size_ID ,
-- ISNULL(c1.Skin_Type_ID ,'''') AS Skin_Type_ID ,
-- ISNULL(c1.Item_Form_ID ,'''') AS Item_Form_ID ,
-- ISNULL(c1.Target_Gender ,'''') AS Target_Gender ,
-- ISNULL(c1.Minimum_Reorder_Level ,0) AS Minimum_Reorder_Level ,
-- ISNULL(c1.Detail_Allow_Online_Display ,'''') AS Detail_Allow_Online_Display ,
-- ISNULL(c1.Seo_Meta_Tag ,'''') AS Seo_Meta_Tag ,
-- ISNULL(c1.Seo_Title ,'''') AS Seo_Title ,
-- ISNULL(c1.Seo_FocusKeyword ,'''') AS Seo_FocusKeyword ,
-- ISNULL(c1.Seo_Meta_Description ,'''') AS Seo_Meta_Description ,
-- ISNULL(c1.Original_Image_Path ,'''') AS Original_Image_Path ,
-- Lm.Designer_Name AS Ledger_Name,
-- ISNULL(CONVERT(DECIMAL(15,2),
-- CONVERT(DECIMAL(15,0),(SELECT SUM(ISNULL(RM.Rating,0)) FROM dbo.Review_Master RM WITH(NOLOCK) WHERE RM.Product_Id= c1.Product_ID))/
-- nullif(CONVERT(DECIMAL(15,0),(SELECT COUNT(1) FROM dbo.Review_Master RM WITH(NOLOCK) WHERE RM.Product_Id=c1.Product_ID)),0)),0) AS Average_Rating
-- ,CONVERT(DECIMAL(15,0),(SELECT COUNT(1) FROM dbo.Review_Master RM WITH(NOLOCK) WHERE RM.Product_Id=c1.Product_ID),0) AS Total_Review,
-- --(SELECT Count(*) FROM Product_Like_Master X WITH(NOLOCK) WHERE X.Product_ID = c1.Product_ID AND Customer_ID='+CAST(@Customer_Id AS VARCHAR(max))+ ' AND Status=1 AND Count > 0) AS AlreadyWishListed ,
-- (SELECT Count(1) FROM UserWishlist_Details X WITH(NOLOCK) WHERE X.Product_ID = c1.Product_ID AND Customer_ID= '+CAST(@Customer_Id AS VARCHAR(max))+ ' AND X.Is_Wholesale=0) AS AlreadyWishListed ,
-- ISNULL((SELECT TOP 1 Product_Like_ID FROM Product_Like_Master X WITH(NOLOCK) WHERE X.Product_ID = c1.Product_ID AND Customer_ID='+CAST(@Customer_Id AS VARCHAR(max))+ ' AND Status = 1 AND Count > 0),0) AS Product_Like_ID,
-- ISNULL((SELECT SUM(Count) FROM Product_Like_Master X WITH(NOLOCK) WHERE X.Product_ID = c1.Product_ID ),0) AS TotalLike,
-- c1.IsActive,
-- ISNULL(Image_Alter, '''') Image_Alter ,
-- ISNULL(Image_Meta_Title, '''') Image_Meta_Title ,
-- ISNULL(Image_Meta_Description, '''') Image_Meta_Description ,
-- ISNULL(Product_Meta_Title, '''') Product_Meta_Title ,
-- ISNULL(Product_Meta_Description, '''') Product_Meta_Description ,
-- ISNULL(Product_Detail_Meta_Title, '''') Product_Detail_Meta_Title ,
-- ISNULL(Product_Detail_Meta_Description, '''') Product_Detail_Meta_Description,cf.Aprrove_Date,
-- (SELECT COUNT(1) FROM ( SELECT om.Offer_ID FROM dbo.Offer_Master om WITH(NOLOCK) WHERE om.Is_Active = 1 AND ( CAST(GETDATE() AS DATE) BETWEEN om.From_Date AND om.To_Date )
-- AND om.Effect_Allow_From_Applicable <=CAST(GETDATE() AS DATE) AND Effect_Product_Wise = 0 UNION ALL
-- SELECT om.Offer_ID FROM dbo.Offer_Master om WITH(NOLOCK) LEFT JOIN dbo.Offer_Detail od WITH ( NOLOCK ) ON od.Offer_ID = om.Offer_ID
-- WHERE om.Is_Active = 1 AND '','' + Product_ID + '','' LIKE ''%,'' + CONVERT(VARCHAR(10), c1.Product_ID) + '',%''
-- AND ( CAST(GETDATE() AS DATE) BETWEEN om.From_Date AND om.To_Date ) AND om.Effect_Allow_From_Applicable <=CAST(GETDATE() AS DATE) ) AS T1)Discount
-- FROM dbo.Catalog_Product_Detail c1 WITH(NOLOCK) INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=c1.Catalog_No
-- LEFT JOIN dbo.Designer_Master Lm WITH(NOLOCK) ON LM.Designer_Id=CAST(cF.Promotional_Brand AS INT)
-- WHERE IsActive=1 AND c1.Detail_Allow_Online_Display=1 AND cF.IsApprove=1 AND ( lm.Is_Active = 1 OR lm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
-- AND c1.Sell_Single_Pcs_Rate > 0 AND c1.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND c1.Product_For != 2 AND c1.Sub_Category_ID <> ''0'' ' +@SubCatwhere ;
SET @SqlStr = 'Select @Cnt = Count(1) FROM (' + @CommonStr +@CommonStr2+ ' ) AS A WHERE 1=1 ' +@where
EXECUTE sp_executesql @SqlStr, N'@Cnt int OUTPUT',@Cnt = @TotalRecords OUTPUT
set @SqlStr='Select Product_ID,Ledger_Name,Catalog_No,Product_SKU,Product_Name ,Description ,Vendor_SKU ,
Vendor_ID,Category_ID ,Sub_Category_ID ,Stock_Available_Till_Date , Avg_Weight_Grams ,Buy_Single_Pcs_Rate ,Buy_Full_Catalog_Rate ,Mu_Percent ,
Sell_Single_Pcs_Rate ,Sell_Full_Catalog_Rate ,Occasion_ID ,Top_Fabrics_ID , Bottom_Fabrics_ID ,Dupatta_Fabrics_ID ,Inner_Fabrics_ID ,Top_Color_ID ,
Bottom_Color_ID ,Dupatta_Color_ID ,Inner_Color_ID , Top_Work_ID ,Bottom_Work_ID ,Dupatta_Work_ID ,Inner_Work_ID ,Packing_Dimensions ,
Sleeve_Style_ID ,Neck_Style_ID ,Product_Tag_ID ,Packing_Type_ID ,Stitching_ID , Stitching_Option_ID ,Product_For ,Product_Size_ID ,Skin_Type_ID ,Item_Form_ID ,
Target_Gender ,Minimum_Reorder_Level ,Detail_Allow_Online_Display ,Seo_Meta_Tag ,Discount, Seo_Title ,Seo_FocusKeyword ,Seo_Meta_Description ,Original_Image_Path ,Average_Rating,Total_Review, AlreadyWishListed ,Product_Like_ID,TotalLike,IsActive ,
Image_Alter,Image_Meta_Title,Image_Meta_Description,Product_Meta_Title,Product_Meta_Description,Product_Detail_Meta_Title,Product_Detail_Meta_Description
,CASE WHEN Stock_Available_Till_Date >= CAST( GETDATE() AS DATE) THEN 0 ELSE 1 END AS IsOutOfStock
FROM (Select *,ROW_NUMBER() OVER(ORDER BY '+@Sort_By + ') AS ROWNUMBER FROM (' + @CommonStr + @CommonStr2 +' ) A WHERE 1=1 ' +@where + ') AS B WHERE ROWNUMBER>='
+ CAST(@StartIndex AS VARCHAR(10)) + ' AND ROWNUMBER <= ' + Cast(@EndIndex as varchar(10));
exec (@SqlStr)
DROP TABLE #Temp
PRINT CAST ( @SqlStr AS NTEXT)
END
(
@StartIndex INT = 1 ,
@EndIndex INT = 8 ,
@Catagory VARCHAR(MAX) = 'GOWN' ,
@Color_Id VARCHAR(50) = '' ,
@Work_Id VARCHAR(50) = '' ,
@Occassion_Id VARCHAR(50) = '' ,
@Fabric_Id VARCHAR(50) = '' ,
@Style_Id VARCHAR(500) = '' ,
@Size_ID VARCHAR(500)='',
@Sort_By VARCHAR(500)='Aprrove_Date desc',
@Min_Price DECIMAL(18,2)=0,
@Max_Price DECIMAL(18,2)=0,
@Designer_ID VARCHAR(500)='',
@Customer_Id INT =0,
@Group_Name VARCHAR(100)='',
@SubCat_Name VARCHAR(100)='',
@TotalRecords INT = 0 OUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SqlStr NVARCHAR(MAX) = '' ,@CommonStr NVARCHAR(MAX) = '',@CommonStr2 NVARCHAR(MAX) = '' ,
@total INT= 0 ;
DECLARE @Catalog_Category_ID INT = 0,@Group_ID INT=0,@SubCat_ID INT =0,@Group_On VARCHAR(20)=''
DECLARE @Search_ID VARCHAR(500)='';
SELECT TOP 1 @Catalog_Category_ID = Catalog_Category_ID FROM dbo.Catalog_Category_Master WITH(NOLOCK) WHERE Category_Name = @Catagory AND Is_Active=1
IF @Group_Name <> ''
BEGIN
SELECT TOP 1 @Group_On = Group_On,@Group_ID=Menu_Group_ID
FROM dbo.Menu_Group_Master WITH(NOLOCK)
WHERE Group_Name = @Group_Name AND Category_ID=@Catalog_Category_ID AND Menu_For='Retail'
IF @Group_On='Category'
BEGIN
SELECT TOP 1 @SubCat_ID = Catalog_Category_ID FROM dbo.Menu_ChildCategory_Master c1 WITH(NOLOCK)
LEFT JOIN dbo.Catalog_Category_Master c2 WITH(NOLOCK) ON c1.Category_ID=c2.Catalog_Category_ID
WHERE Category_Name = @SubCat_Name AND Menu_Group_ID=@Group_ID
END
ELSE IF @Group_On='Attribute'
BEGIN
SELECT TOP 1 @SubCat_ID = Designer_Id FROM dbo.Designer_Master WITH(NOLOCK)
WHERE Designer_Name = @SubCat_Name
END
END
DECLARE @where VARCHAR(MAX)= '' ,@SubCatwhere VARCHAR(MAX)= '' ,
@current INT= 1,@COUNT1 INT = 0 ;
CREATE TABLE #Temp
(
SrNo INT NULL,
Catalog_Category_ID INT NULL
);
IF @Group_On='Category'
BEGIN
IF @SubCat_ID > 0
BEGIN
WITH CTE as
(
SELECT P.Catalog_Category_ID, P.Parent_Category_ID, CAST(P.Category_Name AS VarChar(Max)) as Category_Name
, CAST(P.Parent_Category_ID AS VarChar(Max)) as Parent_Category_IDs
FROM dbo.Catalog_Category_Master P WITH(NOLOCK)
WHERE P.Catalog_Category_ID = @SubCat_ID AND Is_Active=1
UNION ALL
SELECT P1.Catalog_Category_ID, P1.Parent_Category_ID, M.Category_Name + ' >> ' + CAST(P1.Category_Name AS VarChar(Max))
, M.Parent_Category_IDs + ',' + CAST(P1.Parent_Category_ID AS VarChar(Max))
FROM Catalog_Category_Master P1 WITH(NOLOCK)
INNER JOIN CTE M
ON M.Catalog_Category_ID = P1.Parent_Category_ID
AND P1.Is_Active=1
)
INSERT INTO #Temp (SrNo,Catalog_Category_ID)
SELECT ROW_NUMBER() OVER (ORDER BY Catalog_Category_ID),Catalog_Category_ID From CTE
END
ELSE IF @Group_ID > 0
BEGIN
WITH CTE as
(
SELECT P.Group_Category_ID AS Catalog_Category_ID, c3.Parent_Category_ID, CAST(c3.Category_Name AS VarChar(Max)) as Category_Name
, CAST(c3.Parent_Category_ID AS VarChar(Max)) as Parent_Category_IDs
FROM dbo.Menu_Group_Master P WITH(NOLOCK)
LEFT JOIN dbo.Catalog_Category_Master c3 WITH(NOLOCK) ON c3.Catalog_Category_ID=P.Group_Category_ID
WHERE P.Menu_Group_ID = @Group_ID AND Is_Active=1
UNION ALL
SELECT P1.Catalog_Category_ID, P1.Parent_Category_ID, M.Category_Name + ' >> ' + CAST(P1.Category_Name AS VarChar(Max))
, M.Parent_Category_IDs + ',' + CAST(P1.Parent_Category_ID AS VarChar(Max))
FROM Catalog_Category_Master P1 WITH(NOLOCK)
INNER JOIN CTE M
ON M.Catalog_Category_ID = P1.Parent_Category_ID
AND P1.Is_Active=1
)
INSERT INTO #Temp (SrNo,Catalog_Category_ID)
SELECT ROW_NUMBER() OVER (ORDER BY Catalog_Category_ID),Catalog_Category_ID From CTE
END
SELECT @COUNT1 = COUNT(1) FROM #Temp
SET @SubCatwhere=@SubCatwhere+' AND ('
WHILE @COUNT1 >= @current
BEGIN
DECLARE @ID VARCHAR(10)= '' ;
SELECT @ID = Catalog_Category_ID
FROM #Temp
WHERE SrNo = @current ;
IF(@current>1)
BEGIN
SET @SubCatwhere=@SubCatwhere+' OR '
END
SET @SubCatwhere = @SubCatwhere + ' Category_ID='+CONVERT(VARCHAR(10), @ID) +' OR '',''+ Sub_Category_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @ID) + ',%'' ';
SET @current = @current + 1 ;
END
SET @SubCatwhere=@SubCatwhere+' ) '
END
ELSE IF @Group_On='Attribute'
BEGIN
IF @SubCat_ID > 0
BEGIN
SET @SubCatwhere=' AND cF.Promotional_Brand='+CONVERT(VARCHAR(5000), @SubCat_ID) ;
END
ELSE IF @Group_ID > 0
BEGIN
SELECT * ,
ROW_NUMBER() OVER ( ORDER BY Attribute_value ) AS SrNo
INTO #Temp1
FROM ( SELECT Attribute_value
FROM dbo.Menu_ChildCategory_Master WITH(NOLOCK)
WHERE Menu_Group_ID = @Group_ID
) AS T1
SELECT @COUNT1 = COUNT(1) FROM #Temp1
SET @current=1;
SET @SubCatwhere=@SubCatwhere+' AND ('
WHILE @COUNT1 >= @current
BEGIN
DECLARE @ID2 VARCHAR(10)= '' ;
SELECT @ID2 = Attribute_value
FROM #Temp1
WHERE SrNo = @current ;
IF(@current>1)
BEGIN
SET @SubCatwhere=@SubCatwhere+' OR '
END
SET @SubCatwhere = @SubCatwhere + ' cF.Promotional_Brand='+CONVERT(VARCHAR(10), @ID2) ;
SET @current = @current + 1 ;
END
SET @SubCatwhere=@SubCatwhere+' ) '
END
END
ELSE --if only category
BEGIN
WITH CTE as
(
SELECT P.Catalog_Category_ID, P.Parent_Category_ID, CAST(P.Category_Name AS VarChar(Max)) as Category_Name
, CAST(P.Parent_Category_ID AS VarChar(Max)) as Parent_Category_IDs
FROM dbo.Catalog_Category_Master P WITH(NOLOCK)
WHERE P.Catalog_Category_ID = @Catalog_Category_ID AND Is_Active=1
UNION ALL
SELECT P1.Catalog_Category_ID, P1.Parent_Category_ID, M.Category_Name + ' >> ' + CAST(P1.Category_Name AS VarChar(Max))
, M.Parent_Category_IDs + ',' + CAST(P1.Parent_Category_ID AS VarChar(Max))
FROM Catalog_Category_Master P1 WITH(NOLOCK)
INNER JOIN CTE M
ON M.Catalog_Category_ID = P1.Parent_Category_ID
AND P1.Is_Active=1
)
INSERT INTO #Temp (SrNo,Catalog_Category_ID)
SELECT ROW_NUMBER() OVER (ORDER BY Catalog_Category_ID),Catalog_Category_ID From CTE
SELECT @COUNT1 = COUNT(1)
FROM #Temp
SET @current=1;
SET @SubCatwhere=@SubCatwhere+' AND ('
WHILE @COUNT1 >= @current
BEGIN
DECLARE @ID1 VARCHAR(10)= '' ;
SELECT @ID1 = Catalog_Category_ID
FROM #Temp
WHERE SrNo = @current ;
IF(@current>1)
BEGIN
SET @SubCatwhere=@SubCatwhere+' OR '
END
SET @SubCatwhere = @SubCatwhere + ' Category_ID='+CONVERT(VARCHAR(10), @ID1) +' OR '',''+ Sub_Category_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @ID1) + ',%'' ';
SET @current = @current + 1 ;
END
SET @SubCatwhere=@SubCatwhere+' ) '
END
IF ( @Color_Id <> '' )
BEGIN
SELECT * INTO #TempColor FROM dbo.Split(@Color_Id,',')
SET @current=1;
SELECT @COUNT1 = COUNT(1) FROM #TempColor
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' AND ( '
END
WHILE @COUNT1 >= @current
BEGIN
IF(@current>1)
BEGIN
SET @where=@where+' OR '
END
DECLARE @ColorID VARCHAR(10)= '' ;
SELECT @ColorID = Data FROM #TempColor WHERE ID = @current ;
SET @where = @where + '
'',''+ A.Top_Color_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @ColorID) + ',%''
OR
'',''+ A.Bottom_Color_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @ColorID) + ',%''
OR
'',''+ A.Dupatta_Color_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @ColorID) + ',%''
OR
'',''+ A.Inner_Color_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @ColorID) + ',%''
' ;
SET @current = @current + 1 ;
END
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' )'
END
END
IF ( @Work_Id <> '' )
BEGIN
SELECT * INTO #TempWork FROM dbo.Split(@Work_Id,',')
SET @current=1;
SELECT @COUNT1 = COUNT(1) FROM #TempWork
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' AND ( '
END
WHILE @COUNT1 >= @current
BEGIN
DECLARE @WorkID VARCHAR(10)= '' ;
SELECT @WorkID = Data FROM #TempWork WHERE ID = @current ;
IF(@current>1)
BEGIN
SET @where=@where+' OR '
END
SET @where = @where + '
'',''+ A.Top_Work_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @WorkID) + ',%''
OR
'',''+ A.Bottom_Work_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @WorkID) + ',%''
OR
'',''+ A.Dupatta_Work_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @WorkID) + ',%''
OR
'',''+ A.Inner_Work_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @WorkID) + ',%''
' ;
SET @current = @current + 1 ;
END
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' )'
END
END
IF ( @Fabric_Id <> '' )
BEGIN
SELECT * INTO #TempFabric FROM dbo.Split(@Fabric_Id,',')
SET @current=1;
SELECT @COUNT1 = COUNT(1) FROM #TempFabric
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' AND ( '
END
WHILE @COUNT1 >= @current
BEGIN
IF(@current>1)
BEGIN
SET @where=@where+' OR '
END
DECLARE @FabricID VARCHAR(10)= '' ;
SELECT @FabricID = Data FROM #TempFabric WHERE ID = @current ;
SET @where = @where + '
'',''+ A.Top_Fabrics_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @FabricID) + ',%''
OR
'',''+ A.Bottom_Fabrics_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @FabricID) + ',%''
OR
'',''+ A.Dupatta_Fabrics_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @FabricID) + ',%''
OR
'',''+ A.Inner_Fabrics_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @FabricID) + ',%''
' ;
SET @current = @current + 1 ;
END
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' )'
END
END
IF ( @Occassion_Id <> '' )
BEGIN
Declare @TempOccIDs varchar(100)='';
SELECT @TempOccIDs= ISNULL(STUFF(( SELECT ',' + CONVERT(varchar(10),Occasion_ID) FROM dbo.Catalog_Occasion_Master WITH(NOLOCK) WHERE Occasion_Type LIKE '%'+ @Occassion_Id +'%' FOR XML PATH('') ), 1, 1, ''), '')
SELECT * INTO #TempOccasion FROM dbo.Split(@TempOccIDs,',')
SET @current=1;
SELECT @COUNT1 = COUNT(1) FROM #TempOccasion
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' AND ( '
END
WHILE @COUNT1 >= @current
BEGIN
DECLARE @OccasionID VARCHAR(10)= '' ;
SELECT @OccasionID = Data FROM #TempOccasion WHERE ID = @current ;
IF(@current>1)
BEGIN
SET @where=@where+' OR '
END
SET @where = @where + ' '',''+ A.Occasion_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @OccasionID) + ',%'' ';
SET @current = @current + 1 ;
END
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' )'
END
END
IF ( @Style_Id <> '' )
BEGIN
SELECT * INTO #TempStyle FROM dbo.Split(@Style_Id,',')
SET @current=1;
SELECT @COUNT1 = COUNT(1) FROM #TempStyle
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' AND ( '
END
WHILE @COUNT1 >= @current
BEGIN
IF(@current>1)
BEGIN
SET @where=@where+' OR '
END
DECLARE @StyleID VARCHAR(10)= '' ;
SELECT @StyleID = Data FROM #TempStyle WHERE ID = @current ;
SET @where = @where + '
'',''+ A.Sleeve_Style_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @StyleID) + ',%''
OR
'',''+ A.Neck_Style_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @StyleID) + ',%''
'
SET @current = @current + 1 ;
END
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' )'
END
END
IF ( @Size_ID <> '' )
BEGIN
SELECT * INTO #TempSize FROM dbo.Split(@Size_ID,',')
SET @current=1;
SELECT @COUNT1 = COUNT(1) FROM #TempSize
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' AND ( '
END
WHILE @COUNT1 >= @current
BEGIN
IF(@current>1)
BEGIN
SET @where=@where+' OR '
END
DECLARE @SizeID VARCHAR(10)= '' ;
SELECT @SizeID = Data FROM #TempSize WHERE ID = @current ;
SET @where = @where + ' '',''+ A.Product_Size_ID +'','' LIKE ''%,' + CONVERT(VARCHAR(10), @SizeID) + ',%'' ';
SET @current = @current + 1 ;
END
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' ) '
END
END
IF ( @Max_Price > 0 )
BEGIN
SET @where = @where + ' AND (
A.Sell_Single_Pcs_Rate >= ''' + CAST(@Min_Price AS VARCHAR(50)) + '''
AND A.Sell_Single_Pcs_Rate <= ''' + CAST(@Max_Price AS VARCHAR(50)) + ''')'
END
IF ( @Designer_ID <> '' )
BEGIN
SELECT * INTO #TempDesigner FROM dbo.Split(@Designer_ID,',')
SET @current=1;
SELECT @COUNT1 = COUNT(1) FROM #TempDesigner
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' AND ( '
END
WHILE @COUNT1 >= @current
BEGIN
DECLARE @DesID VARCHAR(10)= '' ;
SELECT @DesID = Data FROM #TempDesigner WHERE ID = @current ;
IF(@current>1)
BEGIN
SET @where=@where+' OR '
END
SET @where = @where + ' A.Vendor_ID =' + CONVERT(VARCHAR(10), @DesID)
SET @current = @current + 1 ;
END
IF @COUNT1 > 0
BEGIN
SET @where = @where + ' )'
END
END
SET @CommonStr = '
SELECT ISNULL(c1.Product_ID,0) AS Product_ID ,
ISNULL(c1.Catalog_No,0 ) AS Catalog_No,
ISNULL(c1.Product_SKU ,'''') AS Product_SKU,
ISNULL(c1.Product_Name,'''') AS Product_Name ,
ISNULL(c1.Description ,'''') AS Description ,
ISNULL(c1.Vendor_SKU ,'''') AS Vendor_SKU ,
ISNULL(Lm.Designer_Id,'''') AS Vendor_ID,
ISNULL(c1.Category_ID ,'''') AS Category_ID ,
ISNULL(c1.Sub_Category_ID ,0) AS Sub_Category_ID ,
ISNULL(c1.Stock_Available_Till_Date ,'''') AS Stock_Available_Till_Date ,
ISNULL(c1.Avg_Weight_Grams ,0) AS Avg_Weight_Grams ,
ISNULL(c1.Buy_Single_Pcs_Rate ,0) AS Buy_Single_Pcs_Rate ,
ISNULL(c1.Buy_Full_Catalog_Rate ,0) AS Buy_Full_Catalog_Rate ,
ISNULL(c1.Mu_Percent ,0) AS Mu_Percent ,
ISNULL(c1.Sell_Single_Pcs_Rate ,0) AS Sell_Single_Pcs_Rate ,
ISNULL(c1.Sell_Full_Catalog_Rate ,0) AS Sell_Full_Catalog_Rate ,
ISNULL(c1.Occasion_ID ,'''') AS Occasion_ID ,
ISNULL(c1.Top_Fabrics_ID ,'''') AS Top_Fabrics_ID ,
ISNULL(c1.Bottom_Fabrics_ID ,'''') AS Bottom_Fabrics_ID ,
ISNULL(c1.Dupatta_Fabrics_ID ,'''') AS Dupatta_Fabrics_ID ,
ISNULL(c1.Inner_Fabrics_ID ,'''') AS Inner_Fabrics_ID ,
ISNULL(c1.Top_Color_ID ,'''') AS Top_Color_ID ,
ISNULL(c1.Bottom_Color_ID ,'''') AS Bottom_Color_ID ,
ISNULL(c1.Dupatta_Color_ID ,'''') AS Dupatta_Color_ID ,
ISNULL(c1.Inner_Color_ID ,'''') AS Inner_Color_ID ,
ISNULL(c1.Top_Work_ID ,'''') AS Top_Work_ID ,
ISNULL(c1.Bottom_Work_ID ,'''') AS Bottom_Work_ID ,
ISNULL(c1.Dupatta_Work_ID ,'''') AS Dupatta_Work_ID ,
ISNULL(c1.Inner_Work_ID ,'''') AS Inner_Work_ID ,
ISNULL(c1.Packing_Dimensions ,'''') AS Packing_Dimensions ,
ISNULL(c1.Sleeve_Style_ID ,'''') AS Sleeve_Style_ID ,
ISNULL(c1.Neck_Style_ID ,'''') AS Neck_Style_ID ,
ISNULL(c1.Product_Tag_ID ,'''') AS Product_Tag_ID ,
ISNULL(c1.Packing_Type_ID ,'''') AS Packing_Type_ID ,
ISNULL(c1.Stitching_ID ,0) AS Stitching_ID ,
ISNULL(c1.Stitching_Option_ID ,0) AS Stitching_Option_ID ,
ISNULL(c1.Product_For ,0) AS Product_For ,
ISNULL(c1.Product_Size_ID ,'''') AS Product_Size_ID ,
ISNULL(c1.Skin_Type_ID ,'''') AS Skin_Type_ID ,
ISNULL(c1.Item_Form_ID ,'''') AS Item_Form_ID ,
ISNULL(c1.Target_Gender ,'''') AS Target_Gender ,
ISNULL(c1.Minimum_Reorder_Level ,0) AS Minimum_Reorder_Level ,
ISNULL(c1.Detail_Allow_Online_Display ,'''') AS Detail_Allow_Online_Display ,
ISNULL(c1.Seo_Meta_Tag ,'''') AS Seo_Meta_Tag ,
ISNULL(c1.Seo_Title ,'''') AS Seo_Title ,
ISNULL(c1.Seo_FocusKeyword ,'''') AS Seo_FocusKeyword ,
ISNULL(c1.Seo_Meta_Description ,'''') AS Seo_Meta_Description ,
ISNULL(c1.Original_Image_Path ,'''') AS Original_Image_Path ,
Lm.Designer_Name AS Ledger_Name,
ISNULL(CONVERT(DECIMAL(15,2),
CONVERT(DECIMAL(15,0),(SELECT SUM(ISNULL(RM.Rating,0)) FROM dbo.Review_Master RM WITH(NOLOCK) WHERE RM.Product_Id= c1.Product_ID))/
nullif(CONVERT(DECIMAL(15,0),(SELECT COUNT(1) FROM dbo.Review_Master RM WITH(NOLOCK) WHERE RM.Product_Id=c1.Product_ID)),0)),0) AS Average_Rating
,CONVERT(DECIMAL(15,0),(SELECT COUNT(1) FROM dbo.Review_Master RM WITH(NOLOCK) WHERE RM.Product_Id=c1.Product_ID),0) AS Total_Review,
--(SELECT Count(*) FROM Product_Like_Master X WHERE X.Product_ID = c1.Product_ID AND Customer_ID= '+CAST(@Customer_Id AS VARCHAR(max))+ ' AND Status=1 AND Count > 0) AS AlreadyWishListed ,
(SELECT Count(1) FROM UserWishlist_Details X WITH(NOLOCK) WHERE X.Product_ID = c1.Product_ID AND Customer_ID= '+CAST(@Customer_Id AS VARCHAR(max))+ ' AND X.Is_Wholesale=0) AS AlreadyWishListed ,
ISNULL((SELECT TOP 1 Product_Like_ID FROM Product_Like_Master X WITH(NOLOCK) WHERE X.Product_ID = c1.Product_ID AND Customer_ID='+CAST(@Customer_Id AS VARCHAR(max))+ ' AND Status = 1 AND Count > 0),0) AS Product_Like_ID,
ISNULL((SELECT SUM(Count) FROM Product_Like_Master X WITH(NOLOCK) WHERE X.Product_ID = c1.Product_ID ),0) AS TotalLike,
c1.IsActive,
ISNULL(Image_Alter, '''') Image_Alter ,
ISNULL(Image_Meta_Title, '''') Image_Meta_Title ,
ISNULL(Image_Meta_Description, '''') Image_Meta_Description ,
ISNULL(Product_Meta_Title, '''') Product_Meta_Title ,
ISNULL(Product_Meta_Description, '''') Product_Meta_Description ,
ISNULL(Product_Detail_Meta_Title, '''') Product_Detail_Meta_Title ,
ISNULL(Product_Detail_Meta_Description, '''') Product_Detail_Meta_Description,cf.Aprrove_Date,
(SELECT COUNT(1) FROM ( SELECT om.Offer_ID FROM dbo.Offer_Master om WITH(NOLOCK) WHERE om.Is_Active = 1 AND ( CAST(GETDATE() AS DATE) BETWEEN om.From_Date AND om.To_Date )
AND om.Effect_Allow_From_Applicable <=cast (GETDATE() as DATE) AND Effect_Product_Wise = 0 UNION ALL
SELECT om.Offer_ID FROM dbo.Offer_Master om WITH(NOLOCK) LEFT JOIN dbo.Offer_Detail od WITH ( NOLOCK ) ON od.Offer_ID = om.Offer_ID
WHERE om.Is_Active = 1 AND '','' + Product_ID + '','' LIKE ''%,'' + CONVERT(VARCHAR(10), c1.Product_ID) + '',%''
AND ( CAST(GETDATE() AS DATE) BETWEEN om.From_Date AND om.To_Date ) AND om.Effect_Allow_From_Applicable <=cast (GETDATE() as DATE) ) AS T1)Discount
FROM dbo.Catalog_Product_Detail c1 WITH(NOLOCK)
--INNER JOIN #Temp C3 WITH(NOLOCK) ON C3.Catalog_Category_ID =c1.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=c1.Catalog_No
LEFT JOIN dbo.Designer_Master Lm WITH(NOLOCK) ON LM.Designer_Id=CAST(cF.Promotional_Brand AS INT)
WHERE IsActive=1 AND c1.Detail_Allow_Online_Display=1 AND cF.IsApprove=1 AND ( lm.Is_Active = 1 OR lm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
AND c1.Sell_Single_Pcs_Rate > 0 AND c1.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND c1.Product_For != 2' + @SubCatwhere
--set @CommonStr2 = '
-- UNION
-- SELECT ISNULL(c1.Product_ID,0) AS Product_ID ,
-- ISNULL(c1.Catalog_No,0 ) AS Catalog_No,
-- ISNULL(c1.Product_SKU ,'''') AS Product_SKU,
-- ISNULL(c1.Product_Name,'''') AS Product_Name ,
-- ISNULL(c1.Description ,'''') AS Description ,
-- ISNULL(c1.Vendor_SKU ,'''') AS Vendor_SKU ,
-- ISNULL(Lm.Designer_Id,'''') AS Vendor_ID,
-- ISNULL(c1.Category_ID ,'''') AS Category_ID ,
-- ISNULL(c1.Sub_Category_ID ,0) AS Sub_Category_ID ,
-- ISNULL(c1.Stock_Available_Till_Date ,'''') AS Stock_Available_Till_Date ,
-- ISNULL(c1.Avg_Weight_Grams ,0) AS Avg_Weight_Grams ,
-- ISNULL(c1.Buy_Single_Pcs_Rate ,0) AS Buy_Single_Pcs_Rate ,
-- ISNULL(c1.Buy_Full_Catalog_Rate ,0) AS Buy_Full_Catalog_Rate ,
-- ISNULL(c1.Mu_Percent ,0) AS Mu_Percent ,
-- ISNULL(c1.Sell_Single_Pcs_Rate ,0) AS Sell_Single_Pcs_Rate ,
-- ISNULL(c1.Sell_Full_Catalog_Rate ,0) AS Sell_Full_Catalog_Rate ,
-- ISNULL(c1.Occasion_ID ,'''') AS Occasion_ID ,
-- ISNULL(c1.Top_Fabrics_ID ,'''') AS Top_Fabrics_ID ,
-- ISNULL(c1.Bottom_Fabrics_ID ,'''') AS Bottom_Fabrics_ID ,
-- ISNULL(c1.Dupatta_Fabrics_ID ,'''') AS Dupatta_Fabrics_ID ,
-- ISNULL(c1.Inner_Fabrics_ID ,'''') AS Inner_Fabrics_ID ,
-- ISNULL(c1.Top_Color_ID ,'''') AS Top_Color_ID ,
-- ISNULL(c1.Bottom_Color_ID ,'''') AS Bottom_Color_ID ,
-- ISNULL(c1.Dupatta_Color_ID ,'''') AS Dupatta_Color_ID ,
-- ISNULL(c1.Inner_Color_ID ,'''') AS Inner_Color_ID ,
-- ISNULL(c1.Top_Work_ID ,'''') AS Top_Work_ID ,
-- ISNULL(c1.Bottom_Work_ID ,'''') AS Bottom_Work_ID ,
-- ISNULL(c1.Dupatta_Work_ID ,'''') AS Dupatta_Work_ID ,
-- ISNULL(c1.Inner_Work_ID ,'''') AS Inner_Work_ID ,
-- ISNULL(c1.Packing_Dimensions ,'''') AS Packing_Dimensions ,
-- ISNULL(c1.Sleeve_Style_ID ,'''') AS Sleeve_Style_ID ,
-- ISNULL(c1.Neck_Style_ID ,'''') AS Neck_Style_ID ,
-- ISNULL(c1.Product_Tag_ID ,'''') AS Product_Tag_ID ,
-- ISNULL(c1.Packing_Type_ID ,'''') AS Packing_Type_ID ,
-- ISNULL(c1.Stitching_ID ,0) AS Stitching_ID ,
-- ISNULL(c1.Stitching_Option_ID ,0) AS Stitching_Option_ID ,
-- ISNULL(c1.Product_For ,0) AS Product_For ,
-- ISNULL(c1.Product_Size_ID ,'''') AS Product_Size_ID ,
-- ISNULL(c1.Skin_Type_ID ,'''') AS Skin_Type_ID ,
-- ISNULL(c1.Item_Form_ID ,'''') AS Item_Form_ID ,
-- ISNULL(c1.Target_Gender ,'''') AS Target_Gender ,
-- ISNULL(c1.Minimum_Reorder_Level ,0) AS Minimum_Reorder_Level ,
-- ISNULL(c1.Detail_Allow_Online_Display ,'''') AS Detail_Allow_Online_Display ,
-- ISNULL(c1.Seo_Meta_Tag ,'''') AS Seo_Meta_Tag ,
-- ISNULL(c1.Seo_Title ,'''') AS Seo_Title ,
-- ISNULL(c1.Seo_FocusKeyword ,'''') AS Seo_FocusKeyword ,
-- ISNULL(c1.Seo_Meta_Description ,'''') AS Seo_Meta_Description ,
-- ISNULL(c1.Original_Image_Path ,'''') AS Original_Image_Path ,
-- Lm.Designer_Name AS Ledger_Name,
-- ISNULL(CONVERT(DECIMAL(15,2),
-- CONVERT(DECIMAL(15,0),(SELECT SUM(ISNULL(RM.Rating,0)) FROM dbo.Review_Master RM WITH(NOLOCK) WHERE RM.Product_Id= c1.Product_ID))/
-- nullif(CONVERT(DECIMAL(15,0),(SELECT COUNT(1) FROM dbo.Review_Master RM WITH(NOLOCK) WHERE RM.Product_Id=c1.Product_ID)),0)),0) AS Average_Rating
-- ,CONVERT(DECIMAL(15,0),(SELECT COUNT(1) FROM dbo.Review_Master RM WITH(NOLOCK) WHERE RM.Product_Id=c1.Product_ID),0) AS Total_Review,
-- --(SELECT Count(*) FROM Product_Like_Master X WITH(NOLOCK) WHERE X.Product_ID = c1.Product_ID AND Customer_ID='+CAST(@Customer_Id AS VARCHAR(max))+ ' AND Status=1 AND Count > 0) AS AlreadyWishListed ,
-- (SELECT Count(1) FROM UserWishlist_Details X WITH(NOLOCK) WHERE X.Product_ID = c1.Product_ID AND Customer_ID= '+CAST(@Customer_Id AS VARCHAR(max))+ ' AND X.Is_Wholesale=0) AS AlreadyWishListed ,
-- ISNULL((SELECT TOP 1 Product_Like_ID FROM Product_Like_Master X WITH(NOLOCK) WHERE X.Product_ID = c1.Product_ID AND Customer_ID='+CAST(@Customer_Id AS VARCHAR(max))+ ' AND Status = 1 AND Count > 0),0) AS Product_Like_ID,
-- ISNULL((SELECT SUM(Count) FROM Product_Like_Master X WITH(NOLOCK) WHERE X.Product_ID = c1.Product_ID ),0) AS TotalLike,
-- c1.IsActive,
-- ISNULL(Image_Alter, '''') Image_Alter ,
-- ISNULL(Image_Meta_Title, '''') Image_Meta_Title ,
-- ISNULL(Image_Meta_Description, '''') Image_Meta_Description ,
-- ISNULL(Product_Meta_Title, '''') Product_Meta_Title ,
-- ISNULL(Product_Meta_Description, '''') Product_Meta_Description ,
-- ISNULL(Product_Detail_Meta_Title, '''') Product_Detail_Meta_Title ,
-- ISNULL(Product_Detail_Meta_Description, '''') Product_Detail_Meta_Description,cf.Aprrove_Date,
-- (SELECT COUNT(1) FROM ( SELECT om.Offer_ID FROM dbo.Offer_Master om WITH(NOLOCK) WHERE om.Is_Active = 1 AND ( CAST(GETDATE() AS DATE) BETWEEN om.From_Date AND om.To_Date )
-- AND om.Effect_Allow_From_Applicable <=CAST(GETDATE() AS DATE) AND Effect_Product_Wise = 0 UNION ALL
-- SELECT om.Offer_ID FROM dbo.Offer_Master om WITH(NOLOCK) LEFT JOIN dbo.Offer_Detail od WITH ( NOLOCK ) ON od.Offer_ID = om.Offer_ID
-- WHERE om.Is_Active = 1 AND '','' + Product_ID + '','' LIKE ''%,'' + CONVERT(VARCHAR(10), c1.Product_ID) + '',%''
-- AND ( CAST(GETDATE() AS DATE) BETWEEN om.From_Date AND om.To_Date ) AND om.Effect_Allow_From_Applicable <=CAST(GETDATE() AS DATE) ) AS T1)Discount
-- FROM dbo.Catalog_Product_Detail c1 WITH(NOLOCK) INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=c1.Catalog_No
-- LEFT JOIN dbo.Designer_Master Lm WITH(NOLOCK) ON LM.Designer_Id=CAST(cF.Promotional_Brand AS INT)
-- WHERE IsActive=1 AND c1.Detail_Allow_Online_Display=1 AND cF.IsApprove=1 AND ( lm.Is_Active = 1 OR lm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
-- AND c1.Sell_Single_Pcs_Rate > 0 AND c1.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND c1.Product_For != 2 AND c1.Sub_Category_ID <> ''0'' ' +@SubCatwhere ;
SET @SqlStr = 'Select @Cnt = Count(1) FROM (' + @CommonStr +@CommonStr2+ ' ) AS A WHERE 1=1 ' +@where
EXECUTE sp_executesql @SqlStr, N'@Cnt int OUTPUT',@Cnt = @TotalRecords OUTPUT
set @SqlStr='Select Product_ID,Ledger_Name,Catalog_No,Product_SKU,Product_Name ,Description ,Vendor_SKU ,
Vendor_ID,Category_ID ,Sub_Category_ID ,Stock_Available_Till_Date , Avg_Weight_Grams ,Buy_Single_Pcs_Rate ,Buy_Full_Catalog_Rate ,Mu_Percent ,
Sell_Single_Pcs_Rate ,Sell_Full_Catalog_Rate ,Occasion_ID ,Top_Fabrics_ID , Bottom_Fabrics_ID ,Dupatta_Fabrics_ID ,Inner_Fabrics_ID ,Top_Color_ID ,
Bottom_Color_ID ,Dupatta_Color_ID ,Inner_Color_ID , Top_Work_ID ,Bottom_Work_ID ,Dupatta_Work_ID ,Inner_Work_ID ,Packing_Dimensions ,
Sleeve_Style_ID ,Neck_Style_ID ,Product_Tag_ID ,Packing_Type_ID ,Stitching_ID , Stitching_Option_ID ,Product_For ,Product_Size_ID ,Skin_Type_ID ,Item_Form_ID ,
Target_Gender ,Minimum_Reorder_Level ,Detail_Allow_Online_Display ,Seo_Meta_Tag ,Discount, Seo_Title ,Seo_FocusKeyword ,Seo_Meta_Description ,Original_Image_Path ,Average_Rating,Total_Review, AlreadyWishListed ,Product_Like_ID,TotalLike,IsActive ,
Image_Alter,Image_Meta_Title,Image_Meta_Description,Product_Meta_Title,Product_Meta_Description,Product_Detail_Meta_Title,Product_Detail_Meta_Description
,CASE WHEN Stock_Available_Till_Date >= CAST( GETDATE() AS DATE) THEN 0 ELSE 1 END AS IsOutOfStock
FROM (Select *,ROW_NUMBER() OVER(ORDER BY '+@Sort_By + ') AS ROWNUMBER FROM (' + @CommonStr + @CommonStr2 +' ) A WHERE 1=1 ' +@where + ') AS B WHERE ROWNUMBER>='
+ CAST(@StartIndex AS VARCHAR(10)) + ' AND ROWNUMBER <= ' + Cast(@EndIndex as varchar(10));
exec (@SqlStr)
DROP TABLE #Temp
PRINT CAST ( @SqlStr AS NTEXT)
END
No comments:
Post a Comment