ALTER PROCEDURE dbo.Web_Load_product_Filter
(
@Catagory VARCHAR(MAX) = 'SAREE' ,
@Color_Id VARCHAR(500) = '' ,
@Work_Id VARCHAR(500) = '' ,
@Occassion_Id VARCHAR(500) = '' ,
@Fabric_Id VARCHAR(500) = '' ,
@Style_Id VARCHAR(500) = '' ,
@Size_ID VARCHAR(500)='',
@Designer_ID VARCHAR(500)='',
@Min_Price DECIMAL(18,2)=0,
@Max_Price DECIMAL(18,2)=0,
@Group_Name VARCHAR(100)='',
@SubCat_Name VARCHAR(100)=''
)
AS
SET NOCOUNT ON
DECLARE @Catalog_Category_ID INT=0,@Group_ID INT=0,@SubCat_ID INT =0,@Group_On VARCHAR(20)=''
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_MasterWITH(NOLOCK) WHERE Designer_Name = @SubCat_Name
END
END
DECLARE @where VARCHAR(MAX)= '' ,@SubCatwhere VARCHAR(MAX)= '' ,@current INT= 1,@Designerwhere VARCHAR(MAX)= '' ;
DECLARE @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
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
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
LEFT JOIN dbo.Catalog_Category_Master c3 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
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
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
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
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 @Designerwhere = @Designerwhere + ' AND ( '
END
WHILE @COUNT1 >= @current
BEGIN
DECLARE @DesID VARCHAR(10)= '' ;
SELECT @DesID = Data FROM #TempDesigner WHERE ID = @current ;
IF(@current>1)
BEGIN
SET @Designerwhere=@Designerwhere+' OR '
END
SET @Designerwhere = @Designerwhere + ' ( cF.Promotional_Brand =' + CONVERT(VARCHAR(10), @DesID) + ')'
SET @current = @current + 1 ;
END
IF @COUNT1 > 0
BEGIN
SET @Designerwhere = @Designerwhere + ' )'
END
END
DECLARE @SqlStr NVARCHAR(MAX) = ''
SET @SqlStr=';with tmp(Product_ID,Color_ID, ColorIDs) as (
--SELECT Product_ID,Color_ID, ColorIDs FROM (
select Product_ID
,CAST(LEFT(Top_Color_ID +'',''+ Bottom_Color_ID+'',''+Dupatta_Color_ID+'',''+Inner_Color_ID, CHARINDEX('','',Top_Color_ID +'',''+ Bottom_Color_ID+'',''+Dupatta_Color_ID+'',''+Inner_Color_ID+'','')-1) AS INT) Color_ID,
STUFF(Top_Color_ID+'',''+ Bottom_Color_ID+'',''+Dupatta_Color_ID+'',''+Inner_Color_ID, 1, CHARINDEX('','',Top_Color_ID+'',''+ Bottom_Color_ID+'',''+Dupatta_Color_ID+'',''+Inner_Color_ID+'',''), '''') ColorIDs
from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN #Temp C3 ON C3.Catalog_Category_ID =A.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
LEFT JOIN dbo.Designer_Master dm WITH(NOLOCK) ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0 AND cF.Allow_Online_Display = 1
AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2 '+ @SubCatwhere + @where +@Designerwhere
--+'UNION All
--select Product_ID
--,CAST(LEFT(Top_Color_ID +'',''+ Bottom_Color_ID+'',''+Dupatta_Color_ID+'',''+Inner_Color_ID, CHARINDEX('','',Top_Color_ID +'',''+ Bottom_Color_ID+'',''+Dupatta_Color_ID+'',''+Inner_Color_ID+'','')-1) AS INT) Color_ID,
-- STUFF(Top_Color_ID+'',''+ Bottom_Color_ID+'',''+Dupatta_Color_ID+'',''+Inner_Color_ID, 1, CHARINDEX('','',Top_Color_ID+'',''+ Bottom_Color_ID+'',''+Dupatta_Color_ID+'',''+Inner_Color_ID+'',''), '''') ColorIDs
--from Catalog_Product_Detail A
--INNER JOIN dbo.Catalog_Master cF ON cF.Catalog_No=A.Catalog_No
--LEFT JOIN dbo.Designer_Master dm ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
--WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 AND cF.Allow_Online_Display = 1
--AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) and A.Sell_Single_Pcs_Rate > 0
--AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2 ' + @SubCatwhere + @where+@Designerwhere
--+') AS QQ
+'UNION All
select Product_ID
,CAST(LEFT(ColorIDs, CHARINDEX('','',ColorIDs+'','')-1) AS INT),
STUFF(ColorIDs, 1, CHARINDEX('','',ColorIDs+'',''), '''')
from tmp
where ColorIDs > ''''
)
SELECT Color_ID,Color_Name,Color_Rgbcode,COUNT(1) AS ColorCount FROM (
SELECT Product_ID,X.Color_ID,c2.Color_Name,Color_Rgbcode
from tmp x
LEFT JOIN dbo.Catalog_Color_Master C2 WITH(NOLOCK) ON X.Color_ID=C2.Color_ID
WHERE X.Color_ID>0
GROUP BY Product_ID,X.Color_ID,c2.Color_Name,Color_Rgbcode
) AS Q
GROUP BY Color_ID,Color_Name,Color_Rgbcode
ORDER BY Q.Color_Name'
exec (@SqlStr)
--PRINT cast (@SqlStr as text)
SET @SqlStr=';with tmp1(Product_ID,Work_ID,WorkIDs) as (
--SELECT Product_ID,Work_ID,WorkIDs FROM (
select Product_ID
,CAST(LEFT(Top_Work_ID +'',''+ Bottom_Work_ID+'',''+Dupatta_Work_ID+'',''+Inner_Work_ID, CHARINDEX('','',Top_Work_ID +'',''+ Bottom_Work_ID+'',''+Dupatta_Work_ID+'',''+Inner_Work_ID+'','')-1) AS INT) Work_ID,
STUFF(Top_Work_ID+'',''+ Bottom_Work_ID+'',''+Dupatta_Work_ID+'',''+Inner_Work_ID, 1, CHARINDEX('','',Top_Work_ID+'',''+ Bottom_Work_ID+'',''+Dupatta_Work_ID+'',''+Inner_Work_ID+'',''), '''') WorkIDs
from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN #Temp C3 ON C3.Catalog_Category_ID =A.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
LEFT JOIN dbo.Designer_Master dm WITH(NOLOCK) ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2 '+ @SubCatwhere + @where +@Designerwhere
--+'UNION All
--select Product_ID
--,CAST(LEFT(Top_Work_ID +'',''+ Bottom_Work_ID+'',''+Dupatta_Work_ID+'',''+Inner_Work_ID, CHARINDEX('','',Top_Work_ID +'',''+ Bottom_Work_ID+'',''+Dupatta_Work_ID+'',''+Inner_Work_ID+'','')-1) AS INT) Work_ID,
-- STUFF(Top_Work_ID+'',''+ Bottom_Work_ID+'',''+Dupatta_Work_ID+'',''+Inner_Work_ID, 1, CHARINDEX('','',Top_Work_ID+'',''+ Bottom_Work_ID+'',''+Dupatta_Work_ID+'',''+Inner_Work_ID+'',''), '''') WorkIDs
--from Catalog_Product_Detail A
--INNER JOIN dbo.Catalog_Master cF ON cF.Catalog_No=A.Catalog_No
--LEFT JOIN dbo.Designer_Master dm ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
--WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
--AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
--AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2 ' + @SubCatwhere + @where +@Designerwhere
--+') AS QQ
+'union all
select Product_ID
,CAST(LEFT(WorkIDs, CHARINDEX('','',WorkIDs+'','')-1) AS INT),
STUFF(WorkIDs, 1, CHARINDEX('','',WorkIDs+'',''), '''')
from tmp1
where WorkIDs > ''''
)
SELECT Work_ID,Work_Name,COUNT(1) AS WorkCount FROM (
SELECT Product_ID,X.Work_ID,c2.Work_Name
from tmp1 x
LEFT JOIN dbo.Catalog_Work_Master C2 WITH(NOLOCK) ON X.Work_ID=C2.Work_ID
WHERE X.Work_ID>0
GROUP BY Product_ID,X.Work_ID,c2.Work_Name
) AS Q
GROUP BY Work_ID,Work_Name
ORDER BY Q.Work_Name'
exec (@SqlStr)
--PRINT cast (@SqlStr as text)
SET @SqlStr=';with tmp1(Product_ID,Occasion_ID,OccasionIDs) as (
--SELECT Product_ID,Occasion_ID,OccasionIDs FROM (
select Product_ID
,CAST(LEFT(A.Occasion_ID , CHARINDEX('','',A.Occasion_ID +'','')-1) AS INT) Occasion_ID,
STUFF(A.Occasion_ID, 1, CHARINDEX('','',A.Occasion_ID+'',''), '''') OccasionIDs
from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN #Temp C3 WITH(NOLOCK) ON C3.Catalog_Category_ID =A.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
LEFT JOIN dbo.Designer_Master dm WITH(NOLOCK) ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2'+ @SubCatwhere + @where +@Designerwhere
--+'UNION All
--select Product_ID
--,CAST(LEFT(A.Occasion_ID, CHARINDEX('','',A.Occasion_ID+'','')-1) AS INT) Occasion_ID,
-- STUFF(A.Occasion_ID, 1, CHARINDEX('','',A.Occasion_ID+'',''), '''') OccasionIDs
--from Catalog_Product_Detail A
--INNER JOIN dbo.Catalog_Master cF ON cF.Catalog_No=A.Catalog_No
--LEFT JOIN dbo.Designer_Master dm ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
--WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
--AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
--AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2 ' + @SubCatwhere + @where +@Designerwhere
--+') AS QQ
+'UNION ALL
select Product_ID
,CAST(LEFT(OccasionIDs, CHARINDEX('','',OccasionIDs+'','')-1) AS INT),
STUFF(OccasionIDs, 1, CHARINDEX('','',OccasionIDs+'',''), '''')
from tmp1
where OccasionIDs > ''''
)
SELECT Occasion_ID,Occasion_Type,COUNT(1) AS OccasionCount FROM (
SELECT Product_ID,X.Occasion_ID,c2.Occasion_Type
from tmp1 x
LEFT JOIN dbo.Catalog_Occasion_Master C2 WITH(NOLOCK) ON X.Occasion_ID=C2.Occasion_ID
WHERE X.Occasion_ID>0
GROUP BY Product_ID,X.Occasion_ID,c2.Occasion_Type
) AS Q
GROUP BY Occasion_ID,Occasion_Type
ORDER BY Q.Occasion_Type'
--exec (@SqlStr)
--PRINT cast (@SqlStr as text)
SELECT 0 Occasion_ID,'' AS Occasion_Type,1 AS OccasionCount
SET @SqlStr=';with tmp(Product_ID,Fabric_ID, FabricIDs) as (
--SELECT Product_ID,Fabric_ID, FabricIDs FROM (
select Product_ID
,CAST(LEFT(Top_Fabrics_ID +'',''+ Bottom_Fabrics_ID+'',''+Dupatta_Fabrics_ID+'',''+Inner_Fabrics_ID, CHARINDEX('','',Top_Fabrics_ID +'',''+ Bottom_Fabrics_ID+'',''+Dupatta_Fabrics_ID+'',''+Inner_Fabrics_ID+'','')-1) AS INT) Fabric_ID,
STUFF(Top_Fabrics_ID+'',''+ Bottom_Fabrics_ID+'',''+Dupatta_Fabrics_ID+'',''+Inner_Fabrics_ID, 1, CHARINDEX('','',Top_Fabrics_ID+'',''+ Bottom_Fabrics_ID+'',''+Dupatta_Fabrics_ID+'',''+Inner_Fabrics_ID+'',''), '''') FabricIDs
from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN #Temp C3 ON C3.Catalog_Category_ID =A.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
LEFT JOIN dbo.Designer_Master dm WITH(NOLOCK) ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
AND A.Stock_Available_Till_Date >= CAST (GETDATE() AS DATE) AND A.Product_For != 2'+ @SubCatwhere + @where +@Designerwhere
--+'UNION All
--select Product_ID
--,CAST(LEFT(Top_Fabrics_ID +'',''+ Bottom_Fabrics_ID+'',''+Dupatta_Fabrics_ID+'',''+Inner_Fabrics_ID, CHARINDEX('','',Top_Fabrics_ID +'',''+ Bottom_Fabrics_ID+'',''+Dupatta_Fabrics_ID+'',''+Inner_Fabrics_ID+'','')-1) AS INT) Fabric_ID,
-- STUFF(Top_Fabrics_ID+'',''+ Bottom_Fabrics_ID+'',''+Dupatta_Fabrics_ID+'',''+Inner_Fabrics_ID, 1, CHARINDEX('','',Top_Fabrics_ID+'',''+ Bottom_Fabrics_ID+'',''+Dupatta_Fabrics_ID+'',''+Inner_Fabrics_ID+'',''), '''') FabricIDs
--from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
--LEFT JOIN dbo.Designer_Master dm WITH(NOLOCK) ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
--WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
--AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
--AND A.Stock_Available_Till_Date >= CAST (GETDATE() AS DATE) AND A.Product_For != 2 ' + @SubCatwhere + @where +@Designerwhere
--+') AS QQ
+'union all
select Product_ID
,CAST(LEFT(FabricIDs, CHARINDEX('','',FabricIDs+'','')-1) AS INT),
STUFF(FabricIDs, 1, CHARINDEX('','',FabricIDs+'',''), '''')
from tmp
where FabricIDs > ''''
)
SELECT Fabric_ID,Fabrics_Name,COUNT(1) AS FabricCount FROM (
SELECT Product_ID,X.Fabric_ID,c2.Fabrics_Name
from tmp x
LEFT JOIN dbo.Catalog_Fabrics_Master C2 WITH(NOLOCK) ON X.Fabric_ID=C2.Fabrics_ID
WHERE X.Fabric_ID>0
GROUP BY Product_ID,X.Fabric_ID,c2.Fabrics_Name
) AS Q
GROUP BY Fabric_ID,Fabrics_Name
ORDER BY Q.Fabrics_Name'
exec (@SqlStr)
--PRINT cast (@SqlStr as text)
SET @SqlStr=';with tmp(Product_ID,Style_ID, StyleIDs) as (
--SELECT Product_ID,Style_ID, StyleIDs FROM (
select Product_ID
,CAST(LEFT(Sleeve_Style_ID +'',''+ Neck_Style_ID, CHARINDEX('','',Sleeve_Style_ID +'',''+ Neck_Style_ID+'','')-1) AS INT) Style_ID,
STUFF(Sleeve_Style_ID+'',''+ Neck_Style_ID, 1, CHARINDEX('','',Sleeve_Style_ID+'',''+ Neck_Style_ID+'',''), '''') StyleIDs
from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN #Temp C3 ON C3.Catalog_Category_ID =A.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
LEFT JOIN dbo.Designer_Master dm WITH(NOLOCK) ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2'+ @SubCatwhere + @where +@Designerwhere
--+'UNION All
--select Product_ID
--,CAST(LEFT(Sleeve_Style_ID +'',''+ Neck_Style_ID, CHARINDEX('','',Sleeve_Style_ID +'',''+ Neck_Style_ID+'','')-1) AS INT) Style_ID,
-- STUFF(Sleeve_Style_ID+'',''+ Neck_Style_ID, 1, CHARINDEX('','',Sleeve_Style_ID+'',''+ Neck_Style_ID+'',''), '''') StyleIDs
--from Catalog_Product_Detail A
--INNER JOIN dbo.Catalog_Master cF ON cF.Catalog_No=A.Catalog_No
--LEFT JOIN dbo.Designer_Master dm ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
--WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
--AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
--AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2' + @SubCatwhere + @where +@Designerwhere
--+') AS QQ
+'UNION ALL
select Product_ID
,CAST(LEFT(StyleIDs, CHARINDEX('','',StyleIDs+'','')-1) AS INT),
STUFF(StyleIDs, 1, CHARINDEX('','',StyleIDs+'',''), '''')
from tmp
where StyleIDs > ''''
)
SELECT Style_ID,Style_Name,COUNT(1) AS StyleCount FROM (
SELECT Product_ID,X.Style_ID,c2.Style_Name
from tmp x
LEFT JOIN dbo.Catalog_Style_Master C2 WITH(NOLOCK) ON X.Style_ID=C2.Style_ID
WHERE X.Style_ID>0
GROUP BY Product_ID,X.Style_ID,c2.Style_Name
) AS Q
GROUP BY Style_ID,Style_Name
ORDER BY Q.Style_Name'
exec (@SqlStr)
--PRINT cast (@SqlStr as text)
SET @SqlStr=';with tmp1(Product_ID,Product_Size_ID,ProductSizeIDs) as (
--SELECT Product_ID,Product_Size_ID,ProductSizeIDs FROM (
select Product_ID
,CAST(LEFT(Product_Size_ID , CHARINDEX('','',Product_Size_ID +'','')-1) AS INT) Product_Size_ID,
STUFF(Product_Size_ID, 1, CHARINDEX('','',Product_Size_ID+'',''), '''') ProductSizeIDs
from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN #Temp C3 WITH(NOLOCK) ON C3.Catalog_Category_ID =A.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
LEFT JOIN dbo.Designer_Master dm WITH(NOLOCK) ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2' + @SubCatwhere + @where +@Designerwhere
--+'UNION All
--select Product_ID
--,CAST(LEFT(Product_Size_ID, CHARINDEX('','',Product_Size_ID+'','')-1) AS INT) Product_Size_ID,
-- STUFF(Product_Size_ID, 1, CHARINDEX('','',Product_Size_ID+'',''), '''') ProductSizeIDs
--from Catalog_Product_Detail A
--INNER JOIN dbo.Catalog_Master cF ON cF.Catalog_No=A.Catalog_No
--LEFT JOIN dbo.Designer_Master dm ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
--WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
--AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
--AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2' + @SubCatwhere + @where +@Designerwhere
--+') AS QQ
+'UNION ALL
select Product_ID
,CAST(LEFT(ProductSizeIDs, CHARINDEX('','',ProductSizeIDs+'','')-1) AS INT),
STUFF(ProductSizeIDs, 1, CHARINDEX('','',ProductSizeIDs+'',''), '''')
from tmp1
where ProductSizeIDs > ''''
)
SELECT Product_Size_ID,Size_Name,COUNT(1) AS SizeCount FROM (
SELECT Product_ID,X.Product_Size_ID,c2.Size_Name
from tmp1 x
LEFT JOIN dbo.Catalog_Product_Size_Master C2 WITH(NOLOCK) ON X.Product_Size_ID=C2.Size_ID
WHERE X.Product_Size_ID>0
GROUP BY Product_ID,X.Product_Size_ID,c2.Size_Name
) AS Q
GROUP BY Product_Size_ID,Size_Name
ORDER BY Q.Size_Name'
exec (@SqlStr)
--PRINT cast (@SqlStr as text)
SET @SqlStr='SELEct ISNULL(Designer_Id,0) Designer_Id,ISNULL(Designer_Name,'''') Designer_Name,COUNT(DISTINCT Product_ID) AS Designer_Count
FROM (SELECT Product_ID,Designer_Id,Designer_Name
from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN #Temp C3 ON C3.Catalog_Category_ID =A.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
LEFT JOIN dbo.Designer_Master LM WITH(NOLOCK) ON LM.Designer_Id=CAST(cF.Promotional_Brand AS INT)
WHERE A.IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1
AND ( LM.Is_Active = 1 OR LM.Designer_Name IS NULL ) and A.Sell_Single_Pcs_Rate > 0 AND cF.Allow_Online_Display = 1
AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2'+ @SubCatwhere + @where
--+'UNION All
--select Product_ID,Designer_Id,Designer_Name
--from Catalog_Product_Detail A
--INNER JOIN dbo.Catalog_Master cF ON cF.Catalog_No=A.Catalog_No
--LEFT JOIN dbo.Designer_Master LM ON LM.Designer_Id=CAST(cF.Promotional_Brand AS INT)
--WHERE A.IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1
--AND ( lm.Is_Active = 1 OR lm.Designer_Name IS NULL ) and A.Sell_Single_Pcs_Rate > 0 AND cF.Allow_Online_Display = 1
--AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2' + @SubCatwhere + @where
+') AS QQ
GROUP BY Designer_Id,Designer_Name
ORDER BY Designer_Name'
exec (@SqlStr)
--PRINT cast (@SqlStr as text)
SET @SqlStr='
--SELECT MIN(Min_Sell_Single_Pcs_Rate) Min_Sell_Single_Pcs_Rate, MAX(Max_Sell_Single_Pcs_Rate) Max_Sell_Single_Pcs_Rate
--,SUM(ProductCount) AS TotalProduct FROM (
SElect ISNULL(MIN(Sell_Single_Pcs_Rate),0) Min_Sell_Single_Pcs_Rate,ISNULL(MAX(Sell_Single_Pcs_Rate),0) Max_Sell_Single_Pcs_Rate
,COUNT(Product_ID) AS TotalProduct
from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN #Temp C3 ON C3.Catalog_Category_ID =A.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
LEFT JOIN dbo.Designer_Master dm WITH(NOLOCK) ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2' +@SubCatwhere + @where +@Designerwhere
--+'UNION All
--SElect ISNULL(MIN(Sell_Single_Pcs_Rate),0) Min_Sell_Single_Pcs_Rate,ISNULL(MAX(Sell_Single_Pcs_Rate),0) Max_Sell_Single_Pcs_Rate
--,COUNT(Product_ID) AS ProductCount
--from Catalog_Product_Detail A
--INNER JOIN dbo.Catalog_Master cF ON cF.Catalog_No=A.Catalog_No
--LEFT JOIN dbo.Designer_Master dm ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
--WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
--AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
--AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2' + @SubCatwhere + @where +@Designerwhere
--+') AS QQ'
exec (@SqlStr)
--PRINT cast (@SqlStr as text)
SET @SqlStr=' SELECT
ISNULL(c1.Category_ID, 0) AS Category_ID ,
ISNULL(c1.Filter_By, '''') AS Filter_By ,
ISNULL(c1.Display_Order, 0) AS Display_Order
FROM dbo.Product_Filter_Master c1 WITH(NOLOCK)
WHERE c1.Is_Active=1 AND c1.Category_ID=' + cast(@Catalog_Category_ID as varchar(10))
+' ORDER BY Display_Order'
exec (@SqlStr)
--PRINT cast (@SqlStr as text)
GO
(
@Catagory VARCHAR(MAX) = 'SAREE' ,
@Color_Id VARCHAR(500) = '' ,
@Work_Id VARCHAR(500) = '' ,
@Occassion_Id VARCHAR(500) = '' ,
@Fabric_Id VARCHAR(500) = '' ,
@Style_Id VARCHAR(500) = '' ,
@Size_ID VARCHAR(500)='',
@Designer_ID VARCHAR(500)='',
@Min_Price DECIMAL(18,2)=0,
@Max_Price DECIMAL(18,2)=0,
@Group_Name VARCHAR(100)='',
@SubCat_Name VARCHAR(100)=''
)
AS
SET NOCOUNT ON
DECLARE @Catalog_Category_ID INT=0,@Group_ID INT=0,@SubCat_ID INT =0,@Group_On VARCHAR(20)=''
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_MasterWITH(NOLOCK) WHERE Designer_Name = @SubCat_Name
END
END
DECLARE @where VARCHAR(MAX)= '' ,@SubCatwhere VARCHAR(MAX)= '' ,@current INT= 1,@Designerwhere VARCHAR(MAX)= '' ;
DECLARE @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
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
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
LEFT JOIN dbo.Catalog_Category_Master c3 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
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
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
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
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 @Designerwhere = @Designerwhere + ' AND ( '
END
WHILE @COUNT1 >= @current
BEGIN
DECLARE @DesID VARCHAR(10)= '' ;
SELECT @DesID = Data FROM #TempDesigner WHERE ID = @current ;
IF(@current>1)
BEGIN
SET @Designerwhere=@Designerwhere+' OR '
END
SET @Designerwhere = @Designerwhere + ' ( cF.Promotional_Brand =' + CONVERT(VARCHAR(10), @DesID) + ')'
SET @current = @current + 1 ;
END
IF @COUNT1 > 0
BEGIN
SET @Designerwhere = @Designerwhere + ' )'
END
END
DECLARE @SqlStr NVARCHAR(MAX) = ''
SET @SqlStr=';with tmp(Product_ID,Color_ID, ColorIDs) as (
--SELECT Product_ID,Color_ID, ColorIDs FROM (
select Product_ID
,CAST(LEFT(Top_Color_ID +'',''+ Bottom_Color_ID+'',''+Dupatta_Color_ID+'',''+Inner_Color_ID, CHARINDEX('','',Top_Color_ID +'',''+ Bottom_Color_ID+'',''+Dupatta_Color_ID+'',''+Inner_Color_ID+'','')-1) AS INT) Color_ID,
STUFF(Top_Color_ID+'',''+ Bottom_Color_ID+'',''+Dupatta_Color_ID+'',''+Inner_Color_ID, 1, CHARINDEX('','',Top_Color_ID+'',''+ Bottom_Color_ID+'',''+Dupatta_Color_ID+'',''+Inner_Color_ID+'',''), '''') ColorIDs
from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN #Temp C3 ON C3.Catalog_Category_ID =A.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
LEFT JOIN dbo.Designer_Master dm WITH(NOLOCK) ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0 AND cF.Allow_Online_Display = 1
AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2 '+ @SubCatwhere + @where +@Designerwhere
--+'UNION All
--select Product_ID
--,CAST(LEFT(Top_Color_ID +'',''+ Bottom_Color_ID+'',''+Dupatta_Color_ID+'',''+Inner_Color_ID, CHARINDEX('','',Top_Color_ID +'',''+ Bottom_Color_ID+'',''+Dupatta_Color_ID+'',''+Inner_Color_ID+'','')-1) AS INT) Color_ID,
-- STUFF(Top_Color_ID+'',''+ Bottom_Color_ID+'',''+Dupatta_Color_ID+'',''+Inner_Color_ID, 1, CHARINDEX('','',Top_Color_ID+'',''+ Bottom_Color_ID+'',''+Dupatta_Color_ID+'',''+Inner_Color_ID+'',''), '''') ColorIDs
--from Catalog_Product_Detail A
--INNER JOIN dbo.Catalog_Master cF ON cF.Catalog_No=A.Catalog_No
--LEFT JOIN dbo.Designer_Master dm ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
--WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 AND cF.Allow_Online_Display = 1
--AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) and A.Sell_Single_Pcs_Rate > 0
--AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2 ' + @SubCatwhere + @where+@Designerwhere
--+') AS QQ
+'UNION All
select Product_ID
,CAST(LEFT(ColorIDs, CHARINDEX('','',ColorIDs+'','')-1) AS INT),
STUFF(ColorIDs, 1, CHARINDEX('','',ColorIDs+'',''), '''')
from tmp
where ColorIDs > ''''
)
SELECT Color_ID,Color_Name,Color_Rgbcode,COUNT(1) AS ColorCount FROM (
SELECT Product_ID,X.Color_ID,c2.Color_Name,Color_Rgbcode
from tmp x
LEFT JOIN dbo.Catalog_Color_Master C2 WITH(NOLOCK) ON X.Color_ID=C2.Color_ID
WHERE X.Color_ID>0
GROUP BY Product_ID,X.Color_ID,c2.Color_Name,Color_Rgbcode
) AS Q
GROUP BY Color_ID,Color_Name,Color_Rgbcode
ORDER BY Q.Color_Name'
exec (@SqlStr)
--PRINT cast (@SqlStr as text)
SET @SqlStr=';with tmp1(Product_ID,Work_ID,WorkIDs) as (
--SELECT Product_ID,Work_ID,WorkIDs FROM (
select Product_ID
,CAST(LEFT(Top_Work_ID +'',''+ Bottom_Work_ID+'',''+Dupatta_Work_ID+'',''+Inner_Work_ID, CHARINDEX('','',Top_Work_ID +'',''+ Bottom_Work_ID+'',''+Dupatta_Work_ID+'',''+Inner_Work_ID+'','')-1) AS INT) Work_ID,
STUFF(Top_Work_ID+'',''+ Bottom_Work_ID+'',''+Dupatta_Work_ID+'',''+Inner_Work_ID, 1, CHARINDEX('','',Top_Work_ID+'',''+ Bottom_Work_ID+'',''+Dupatta_Work_ID+'',''+Inner_Work_ID+'',''), '''') WorkIDs
from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN #Temp C3 ON C3.Catalog_Category_ID =A.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
LEFT JOIN dbo.Designer_Master dm WITH(NOLOCK) ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2 '+ @SubCatwhere + @where +@Designerwhere
--+'UNION All
--select Product_ID
--,CAST(LEFT(Top_Work_ID +'',''+ Bottom_Work_ID+'',''+Dupatta_Work_ID+'',''+Inner_Work_ID, CHARINDEX('','',Top_Work_ID +'',''+ Bottom_Work_ID+'',''+Dupatta_Work_ID+'',''+Inner_Work_ID+'','')-1) AS INT) Work_ID,
-- STUFF(Top_Work_ID+'',''+ Bottom_Work_ID+'',''+Dupatta_Work_ID+'',''+Inner_Work_ID, 1, CHARINDEX('','',Top_Work_ID+'',''+ Bottom_Work_ID+'',''+Dupatta_Work_ID+'',''+Inner_Work_ID+'',''), '''') WorkIDs
--from Catalog_Product_Detail A
--INNER JOIN dbo.Catalog_Master cF ON cF.Catalog_No=A.Catalog_No
--LEFT JOIN dbo.Designer_Master dm ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
--WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
--AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
--AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2 ' + @SubCatwhere + @where +@Designerwhere
--+') AS QQ
+'union all
select Product_ID
,CAST(LEFT(WorkIDs, CHARINDEX('','',WorkIDs+'','')-1) AS INT),
STUFF(WorkIDs, 1, CHARINDEX('','',WorkIDs+'',''), '''')
from tmp1
where WorkIDs > ''''
)
SELECT Work_ID,Work_Name,COUNT(1) AS WorkCount FROM (
SELECT Product_ID,X.Work_ID,c2.Work_Name
from tmp1 x
LEFT JOIN dbo.Catalog_Work_Master C2 WITH(NOLOCK) ON X.Work_ID=C2.Work_ID
WHERE X.Work_ID>0
GROUP BY Product_ID,X.Work_ID,c2.Work_Name
) AS Q
GROUP BY Work_ID,Work_Name
ORDER BY Q.Work_Name'
exec (@SqlStr)
--PRINT cast (@SqlStr as text)
SET @SqlStr=';with tmp1(Product_ID,Occasion_ID,OccasionIDs) as (
--SELECT Product_ID,Occasion_ID,OccasionIDs FROM (
select Product_ID
,CAST(LEFT(A.Occasion_ID , CHARINDEX('','',A.Occasion_ID +'','')-1) AS INT) Occasion_ID,
STUFF(A.Occasion_ID, 1, CHARINDEX('','',A.Occasion_ID+'',''), '''') OccasionIDs
from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN #Temp C3 WITH(NOLOCK) ON C3.Catalog_Category_ID =A.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
LEFT JOIN dbo.Designer_Master dm WITH(NOLOCK) ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2'+ @SubCatwhere + @where +@Designerwhere
--+'UNION All
--select Product_ID
--,CAST(LEFT(A.Occasion_ID, CHARINDEX('','',A.Occasion_ID+'','')-1) AS INT) Occasion_ID,
-- STUFF(A.Occasion_ID, 1, CHARINDEX('','',A.Occasion_ID+'',''), '''') OccasionIDs
--from Catalog_Product_Detail A
--INNER JOIN dbo.Catalog_Master cF ON cF.Catalog_No=A.Catalog_No
--LEFT JOIN dbo.Designer_Master dm ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
--WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
--AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
--AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2 ' + @SubCatwhere + @where +@Designerwhere
--+') AS QQ
+'UNION ALL
select Product_ID
,CAST(LEFT(OccasionIDs, CHARINDEX('','',OccasionIDs+'','')-1) AS INT),
STUFF(OccasionIDs, 1, CHARINDEX('','',OccasionIDs+'',''), '''')
from tmp1
where OccasionIDs > ''''
)
SELECT Occasion_ID,Occasion_Type,COUNT(1) AS OccasionCount FROM (
SELECT Product_ID,X.Occasion_ID,c2.Occasion_Type
from tmp1 x
LEFT JOIN dbo.Catalog_Occasion_Master C2 WITH(NOLOCK) ON X.Occasion_ID=C2.Occasion_ID
WHERE X.Occasion_ID>0
GROUP BY Product_ID,X.Occasion_ID,c2.Occasion_Type
) AS Q
GROUP BY Occasion_ID,Occasion_Type
ORDER BY Q.Occasion_Type'
--exec (@SqlStr)
--PRINT cast (@SqlStr as text)
SELECT 0 Occasion_ID,'' AS Occasion_Type,1 AS OccasionCount
SET @SqlStr=';with tmp(Product_ID,Fabric_ID, FabricIDs) as (
--SELECT Product_ID,Fabric_ID, FabricIDs FROM (
select Product_ID
,CAST(LEFT(Top_Fabrics_ID +'',''+ Bottom_Fabrics_ID+'',''+Dupatta_Fabrics_ID+'',''+Inner_Fabrics_ID, CHARINDEX('','',Top_Fabrics_ID +'',''+ Bottom_Fabrics_ID+'',''+Dupatta_Fabrics_ID+'',''+Inner_Fabrics_ID+'','')-1) AS INT) Fabric_ID,
STUFF(Top_Fabrics_ID+'',''+ Bottom_Fabrics_ID+'',''+Dupatta_Fabrics_ID+'',''+Inner_Fabrics_ID, 1, CHARINDEX('','',Top_Fabrics_ID+'',''+ Bottom_Fabrics_ID+'',''+Dupatta_Fabrics_ID+'',''+Inner_Fabrics_ID+'',''), '''') FabricIDs
from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN #Temp C3 ON C3.Catalog_Category_ID =A.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
LEFT JOIN dbo.Designer_Master dm WITH(NOLOCK) ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
AND A.Stock_Available_Till_Date >= CAST (GETDATE() AS DATE) AND A.Product_For != 2'+ @SubCatwhere + @where +@Designerwhere
--+'UNION All
--select Product_ID
--,CAST(LEFT(Top_Fabrics_ID +'',''+ Bottom_Fabrics_ID+'',''+Dupatta_Fabrics_ID+'',''+Inner_Fabrics_ID, CHARINDEX('','',Top_Fabrics_ID +'',''+ Bottom_Fabrics_ID+'',''+Dupatta_Fabrics_ID+'',''+Inner_Fabrics_ID+'','')-1) AS INT) Fabric_ID,
-- STUFF(Top_Fabrics_ID+'',''+ Bottom_Fabrics_ID+'',''+Dupatta_Fabrics_ID+'',''+Inner_Fabrics_ID, 1, CHARINDEX('','',Top_Fabrics_ID+'',''+ Bottom_Fabrics_ID+'',''+Dupatta_Fabrics_ID+'',''+Inner_Fabrics_ID+'',''), '''') FabricIDs
--from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
--LEFT JOIN dbo.Designer_Master dm WITH(NOLOCK) ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
--WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
--AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
--AND A.Stock_Available_Till_Date >= CAST (GETDATE() AS DATE) AND A.Product_For != 2 ' + @SubCatwhere + @where +@Designerwhere
--+') AS QQ
+'union all
select Product_ID
,CAST(LEFT(FabricIDs, CHARINDEX('','',FabricIDs+'','')-1) AS INT),
STUFF(FabricIDs, 1, CHARINDEX('','',FabricIDs+'',''), '''')
from tmp
where FabricIDs > ''''
)
SELECT Fabric_ID,Fabrics_Name,COUNT(1) AS FabricCount FROM (
SELECT Product_ID,X.Fabric_ID,c2.Fabrics_Name
from tmp x
LEFT JOIN dbo.Catalog_Fabrics_Master C2 WITH(NOLOCK) ON X.Fabric_ID=C2.Fabrics_ID
WHERE X.Fabric_ID>0
GROUP BY Product_ID,X.Fabric_ID,c2.Fabrics_Name
) AS Q
GROUP BY Fabric_ID,Fabrics_Name
ORDER BY Q.Fabrics_Name'
exec (@SqlStr)
--PRINT cast (@SqlStr as text)
SET @SqlStr=';with tmp(Product_ID,Style_ID, StyleIDs) as (
--SELECT Product_ID,Style_ID, StyleIDs FROM (
select Product_ID
,CAST(LEFT(Sleeve_Style_ID +'',''+ Neck_Style_ID, CHARINDEX('','',Sleeve_Style_ID +'',''+ Neck_Style_ID+'','')-1) AS INT) Style_ID,
STUFF(Sleeve_Style_ID+'',''+ Neck_Style_ID, 1, CHARINDEX('','',Sleeve_Style_ID+'',''+ Neck_Style_ID+'',''), '''') StyleIDs
from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN #Temp C3 ON C3.Catalog_Category_ID =A.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
LEFT JOIN dbo.Designer_Master dm WITH(NOLOCK) ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2'+ @SubCatwhere + @where +@Designerwhere
--+'UNION All
--select Product_ID
--,CAST(LEFT(Sleeve_Style_ID +'',''+ Neck_Style_ID, CHARINDEX('','',Sleeve_Style_ID +'',''+ Neck_Style_ID+'','')-1) AS INT) Style_ID,
-- STUFF(Sleeve_Style_ID+'',''+ Neck_Style_ID, 1, CHARINDEX('','',Sleeve_Style_ID+'',''+ Neck_Style_ID+'',''), '''') StyleIDs
--from Catalog_Product_Detail A
--INNER JOIN dbo.Catalog_Master cF ON cF.Catalog_No=A.Catalog_No
--LEFT JOIN dbo.Designer_Master dm ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
--WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
--AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
--AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2' + @SubCatwhere + @where +@Designerwhere
--+') AS QQ
+'UNION ALL
select Product_ID
,CAST(LEFT(StyleIDs, CHARINDEX('','',StyleIDs+'','')-1) AS INT),
STUFF(StyleIDs, 1, CHARINDEX('','',StyleIDs+'',''), '''')
from tmp
where StyleIDs > ''''
)
SELECT Style_ID,Style_Name,COUNT(1) AS StyleCount FROM (
SELECT Product_ID,X.Style_ID,c2.Style_Name
from tmp x
LEFT JOIN dbo.Catalog_Style_Master C2 WITH(NOLOCK) ON X.Style_ID=C2.Style_ID
WHERE X.Style_ID>0
GROUP BY Product_ID,X.Style_ID,c2.Style_Name
) AS Q
GROUP BY Style_ID,Style_Name
ORDER BY Q.Style_Name'
exec (@SqlStr)
--PRINT cast (@SqlStr as text)
SET @SqlStr=';with tmp1(Product_ID,Product_Size_ID,ProductSizeIDs) as (
--SELECT Product_ID,Product_Size_ID,ProductSizeIDs FROM (
select Product_ID
,CAST(LEFT(Product_Size_ID , CHARINDEX('','',Product_Size_ID +'','')-1) AS INT) Product_Size_ID,
STUFF(Product_Size_ID, 1, CHARINDEX('','',Product_Size_ID+'',''), '''') ProductSizeIDs
from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN #Temp C3 WITH(NOLOCK) ON C3.Catalog_Category_ID =A.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
LEFT JOIN dbo.Designer_Master dm WITH(NOLOCK) ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2' + @SubCatwhere + @where +@Designerwhere
--+'UNION All
--select Product_ID
--,CAST(LEFT(Product_Size_ID, CHARINDEX('','',Product_Size_ID+'','')-1) AS INT) Product_Size_ID,
-- STUFF(Product_Size_ID, 1, CHARINDEX('','',Product_Size_ID+'',''), '''') ProductSizeIDs
--from Catalog_Product_Detail A
--INNER JOIN dbo.Catalog_Master cF ON cF.Catalog_No=A.Catalog_No
--LEFT JOIN dbo.Designer_Master dm ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
--WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
--AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
--AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2' + @SubCatwhere + @where +@Designerwhere
--+') AS QQ
+'UNION ALL
select Product_ID
,CAST(LEFT(ProductSizeIDs, CHARINDEX('','',ProductSizeIDs+'','')-1) AS INT),
STUFF(ProductSizeIDs, 1, CHARINDEX('','',ProductSizeIDs+'',''), '''')
from tmp1
where ProductSizeIDs > ''''
)
SELECT Product_Size_ID,Size_Name,COUNT(1) AS SizeCount FROM (
SELECT Product_ID,X.Product_Size_ID,c2.Size_Name
from tmp1 x
LEFT JOIN dbo.Catalog_Product_Size_Master C2 WITH(NOLOCK) ON X.Product_Size_ID=C2.Size_ID
WHERE X.Product_Size_ID>0
GROUP BY Product_ID,X.Product_Size_ID,c2.Size_Name
) AS Q
GROUP BY Product_Size_ID,Size_Name
ORDER BY Q.Size_Name'
exec (@SqlStr)
--PRINT cast (@SqlStr as text)
SET @SqlStr='SELEct ISNULL(Designer_Id,0) Designer_Id,ISNULL(Designer_Name,'''') Designer_Name,COUNT(DISTINCT Product_ID) AS Designer_Count
FROM (SELECT Product_ID,Designer_Id,Designer_Name
from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN #Temp C3 ON C3.Catalog_Category_ID =A.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
LEFT JOIN dbo.Designer_Master LM WITH(NOLOCK) ON LM.Designer_Id=CAST(cF.Promotional_Brand AS INT)
WHERE A.IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1
AND ( LM.Is_Active = 1 OR LM.Designer_Name IS NULL ) and A.Sell_Single_Pcs_Rate > 0 AND cF.Allow_Online_Display = 1
AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2'+ @SubCatwhere + @where
--+'UNION All
--select Product_ID,Designer_Id,Designer_Name
--from Catalog_Product_Detail A
--INNER JOIN dbo.Catalog_Master cF ON cF.Catalog_No=A.Catalog_No
--LEFT JOIN dbo.Designer_Master LM ON LM.Designer_Id=CAST(cF.Promotional_Brand AS INT)
--WHERE A.IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1
--AND ( lm.Is_Active = 1 OR lm.Designer_Name IS NULL ) and A.Sell_Single_Pcs_Rate > 0 AND cF.Allow_Online_Display = 1
--AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2' + @SubCatwhere + @where
+') AS QQ
GROUP BY Designer_Id,Designer_Name
ORDER BY Designer_Name'
exec (@SqlStr)
--PRINT cast (@SqlStr as text)
SET @SqlStr='
--SELECT MIN(Min_Sell_Single_Pcs_Rate) Min_Sell_Single_Pcs_Rate, MAX(Max_Sell_Single_Pcs_Rate) Max_Sell_Single_Pcs_Rate
--,SUM(ProductCount) AS TotalProduct FROM (
SElect ISNULL(MIN(Sell_Single_Pcs_Rate),0) Min_Sell_Single_Pcs_Rate,ISNULL(MAX(Sell_Single_Pcs_Rate),0) Max_Sell_Single_Pcs_Rate
,COUNT(Product_ID) AS TotalProduct
from Catalog_Product_Detail A WITH(NOLOCK)
--INNER JOIN #Temp C3 ON C3.Catalog_Category_ID =A.Category_ID
INNER JOIN dbo.Catalog_Master cF WITH(NOLOCK) ON cF.Catalog_No=A.Catalog_No
LEFT JOIN dbo.Designer_Master dm WITH(NOLOCK) ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2' +@SubCatwhere + @where +@Designerwhere
--+'UNION All
--SElect ISNULL(MIN(Sell_Single_Pcs_Rate),0) Min_Sell_Single_Pcs_Rate,ISNULL(MAX(Sell_Single_Pcs_Rate),0) Max_Sell_Single_Pcs_Rate
--,COUNT(Product_ID) AS ProductCount
--from Catalog_Product_Detail A
--INNER JOIN dbo.Catalog_Master cF ON cF.Catalog_No=A.Catalog_No
--LEFT JOIN dbo.Designer_Master dm ON dm.Designer_Id= CAST(cf.Promotional_Brand AS INT)
--WHERE IsActive=1 AND A.Detail_Allow_Online_Display=1 and cF.IsApprove = 1 and A.Sell_Single_Pcs_Rate > 0
--AND ( dm.Is_Active = 1 OR dm.Designer_Name IS NULL ) AND cF.Allow_Online_Display = 1
--AND A.Stock_Available_Till_Date >= cast (GETDATE() as DATE) AND A.Product_For != 2' + @SubCatwhere + @where +@Designerwhere
--+') AS QQ'
exec (@SqlStr)
--PRINT cast (@SqlStr as text)
SET @SqlStr=' SELECT
ISNULL(c1.Category_ID, 0) AS Category_ID ,
ISNULL(c1.Filter_By, '''') AS Filter_By ,
ISNULL(c1.Display_Order, 0) AS Display_Order
FROM dbo.Product_Filter_Master c1 WITH(NOLOCK)
WHERE c1.Is_Active=1 AND c1.Category_ID=' + cast(@Catalog_Category_ID as varchar(10))
+' ORDER BY Display_Order'
exec (@SqlStr)
--PRINT cast (@SqlStr as text)
GO
