get widget information in BSK

Welcome to TagMyCode

Please login or create account to add a snippet.

Follow @TagMyCode

SELECT  CorePageName AS ArticleName ,
        T.WidgetTypeName ,
        WidgetPageName ,
        ZonePosition ,
        CoreCreated ,
        CoreFirstPublish
FROM    ( SELECT    *
          FROM      ( SELECT    w.* ,
                                ( SELECT    c.CorePageTypeName
                                  FROM      dbo.CorePage c
                                  WHERE     WidgetPageID = c.CoreID
                                ) WidgetTypeName ,
                                ( SELECT TOP 1
                                            cpvw.CorePageName
                                  FROM      dbo.CorePageVersion cpvw
                                  WHERE     WidgetPageID = cpvw.CorePageID
                                ) WidgetPageName ,
                                cp.CoreID ,
                                cpv.CoreVersionID ,
                                'ArticleBottomWidget' AS ZonePosition ,
                                cp.CoreGuid ,
                                ( SELECT    cr.CoreGuid
                                  FROM      dbo.CorePage cr
                                  WHERE     cr.CoreID = w.WidgetPageID
                                ) WidgetGuid ,
                                cpv.CorePageName ,
                                cp.CoreCreated ,
                                cpv.CoreFirstPublish
                      FROM      dbo.ArticleBottomWidget w
                                INNER JOIN dbo.CorePageVersion cpv ON w.CoreArticleVersionID = cpv.CoreVersionID
                                INNER JOIN dbo.CorePage cp ON cpv.CorePageID = cp.CoreID
                      WHERE     cp.CoreRemoteSite = 'AmeliaCore'  
                    ) AS tmp1
                    INNER JOIN ( SELECT cpv.CorePageID ,
                                        MAX(cpv.CoreVersionID) AS LastVersionID
                                 FROM   dbo.CorePageVersion cpv
                                  WHERE cpv.CoreStatus=4
                                 GROUP BY cpv.CorePageID
                               ) AS tmp2 ON tmp1.CoreID = tmp2.CorePageID AND tmp1.CoreVersionID = tmp2.LastVersionID
          UNION ALL
          SELECT    *
          FROM      ( SELECT    w.* ,
                                ( SELECT    c.CorePageTypeName
                                  FROM      dbo.CorePage c
                                  WHERE     WidgetPageID = c.CoreID
                                ) WidgetTypeName ,
                                ( SELECT TOP 1
                                            cpvw.CorePageName
                                  FROM      dbo.CorePageVersion cpvw
                                  WHERE     WidgetPageID = cpvw.CorePageID
                                ) WidgetPageName ,
                                cp.CoreID ,
                                cpv.CoreVersionID ,
                                'ArticleRightWidget' AS ZonePosition ,
                                cp.CoreGuid ,
                                ( SELECT    cr.CoreGuid
                                  FROM      dbo.CorePage cr
                                  WHERE     cr.CoreID = w.WidgetPageID
                                ) WidgetGuid ,
                                cpv.CorePageName ,
                                cp.CoreCreated ,
                                cpv.CoreFirstPublish
                      FROM      dbo.ArticleRightWidget w
                                INNER JOIN dbo.CorePageVersion cpv ON w.CoreArticleVersionID = cpv.CoreVersionID
                                INNER JOIN dbo.CorePage cp ON cpv.CorePageID = cp.CoreID
                      WHERE     cp.CoreRemoteSite = 'AmeliaCore'
                    ) AS tmp1
                    INNER JOIN ( SELECT cpv.CorePageID ,
                                        MAX(cpv.CoreVersionID) AS LastVersionID
                                 FROM   dbo.CorePageVersion cpv
                                 WHERE cpv.CoreStatus=4
                                 GROUP BY cpv.CorePageID
                               ) AS tmp2 ON tmp1.CoreID = tmp2.CorePageID AND tmp1.CoreVersionID = tmp2.LastVersionID
          UNION ALL
          SELECT    *
          FROM      ( SELECT    w.* ,
                                ( SELECT    c.CorePageTypeName
                                  FROM      dbo.CorePage c
                                  WHERE     WidgetPageID = c.CoreID
                                ) WidgetTypeName ,
                                ( SELECT TOP 1
                                            cpvw.CorePageName
                                  FROM      dbo.CorePageVersion cpvw
                                  WHERE     WidgetPageID = cpvw.CorePageID
                                ) WidgetPageName ,
                                cp.CoreID ,
                                cpv.CoreVersionID ,
                                'ArticleTopWidget' AS ZonePosition ,
                                cp.CoreGuid ,
                                ( SELECT    cr.CoreGuid
                                  FROM      dbo.CorePage cr
                                  WHERE     cr.CoreID = w.WidgetPageID
                                ) WidgetGuid ,
                                cpv.CorePageName ,
                                cp.CoreCreated ,
                                cpv.CoreFirstPublish
                      FROM      dbo.ArticleTopWidget w
                                INNER JOIN dbo.CorePageVersion cpv ON w.CoreArticleVersionID = cpv.CoreVersionID
                                INNER JOIN dbo.CorePage cp ON cpv.CorePageID = cp.CoreID
                      WHERE     cp.CoreRemoteSite = 'AmeliaCore'
                    ) AS tmp1
                    INNER JOIN ( SELECT cpv.CorePageID ,
                                        MAX(cpv.CoreVersionID) AS LastVersionID
                                 FROM   dbo.CorePageVersion cpv
                                  WHERE cpv.CoreStatus=4
                                 GROUP BY cpv.CorePageID
                               ) AS tmp2 ON tmp1.CoreID = tmp2.CorePageID AND tmp1.CoreVersionID = tmp2.LastVersionID
        ) AS T
WHERE   T.WidgetTypeName  IN ('[Widget] Shop image gallery widget','[Widget] Factbox','[Widget] Html','[Widget] Quick poll widget','[Widget] Quiz widget')

--IS NOT NULL --AND T.CorePageID < 110683 --(T.CorePageID>=113351 AND T.CorePageID<=110683000)

ORDER BY T.CorePageID

Language: SQL

Posted by: Trần Hải Quân

Added: May 13, 2013 3:38 AM

Modified: Jul 19, 2013 6:56 AM

Tags: sql episerver

SELECT CorePageName AS ArticleName ,
T.WidgetTypeName ,
WidgetPageName ,
ZonePosition ,
CoreCreated ,
CoreFirstPublish
FROM ( SELECT *
FROM ( SELECT w.* ,
( SELECT c.CorePageTypeName
FROM dbo.CorePage c
WHERE WidgetPageID = c.CoreID
) WidgetTypeName ,
( SELECT TOP 1
cpvw.CorePageName
FROM dbo.CorePageVersion cpvw
WHERE WidgetPageID = cpvw.CorePageID
) WidgetPageName ,
cp.CoreID ,
cpv.CoreVersionID ,
'ArticleBottomWidget' AS ZonePosition ,
cp.CoreGuid ,
( SELECT cr.CoreGuid
FROM dbo.CorePage cr
WHERE cr.CoreID = w.WidgetPageID
) WidgetGuid ,
cpv.CorePageName ,
cp.CoreCreated ,
cpv.CoreFirstPublish
FROM dbo.ArticleBottomWidget w
INNER JOIN dbo.CorePageVersion cpv ON w.CoreArticleVersionID = cpv.CoreVersionID
INNER JOIN dbo.CorePage cp ON cpv.CorePageID = cp.CoreID
WHERE cp.CoreRemoteSite = 'AmeliaCore'
) AS tmp1
INNER JOIN ( SELECT cpv.CorePageID ,
MAX(cpv.CoreVersionID) AS LastVersionID
FROM dbo.CorePageVersion cpv
WHERE cpv.CoreStatus=4
GROUP BY cpv.CorePageID
) AS tmp2 ON tmp1.CoreID = tmp2.CorePageID AND tmp1.CoreVersionID = tmp2.LastVersionID
UNION ALL
SELECT *
FROM ( SELECT w.* ,
( SELECT c.CorePageTypeName
FROM dbo.CorePage c
WHERE WidgetPageID = c.CoreID
) WidgetTypeName ,
( SELECT TOP 1
cpvw.CorePageName
FROM dbo.CorePageVersion cpvw
WHERE WidgetPageID = cpvw.CorePageID
) WidgetPageName ,
cp.CoreID ,
cpv.CoreVersionID ,
'ArticleRightWidget' AS ZonePosition ,
cp.CoreGuid ,
( SELECT cr.CoreGuid
FROM dbo.CorePage cr
WHERE cr.CoreID = w.WidgetPageID
) WidgetGuid ,
cpv.CorePageName ,
cp.CoreCreated ,
cpv.CoreFirstPublish
FROM dbo.ArticleRightWidget w
INNER JOIN dbo.CorePageVersion cpv ON w.CoreArticleVersionID = cpv.CoreVersionID
INNER JOIN dbo.CorePage cp ON cpv.CorePageID = cp.CoreID
WHERE cp.CoreRemoteSite = 'AmeliaCore'
) AS tmp1
INNER JOIN ( SELECT cpv.CorePageID ,
MAX(cpv.CoreVersionID) AS LastVersionID
FROM dbo.CorePageVersion cpv
WHERE cpv.CoreStatus=4
GROUP BY cpv.CorePageID
) AS tmp2 ON tmp1.CoreID = tmp2.CorePageID AND tmp1.CoreVersionID = tmp2.LastVersionID
UNION ALL
SELECT *
FROM ( SELECT w.* ,
( SELECT c.CorePageTypeName
FROM dbo.CorePage c
WHERE WidgetPageID = c.CoreID
) WidgetTypeName ,
( SELECT TOP 1
cpvw.CorePageName
FROM dbo.CorePageVersion cpvw
WHERE WidgetPageID = cpvw.CorePageID
) WidgetPageName ,
cp.CoreID ,
cpv.CoreVersionID ,
'ArticleTopWidget' AS ZonePosition ,
cp.CoreGuid ,
( SELECT cr.CoreGuid
FROM dbo.CorePage cr
WHERE cr.CoreID = w.WidgetPageID
) WidgetGuid ,
cpv.CorePageName ,
cp.CoreCreated ,
cpv.CoreFirstPublish
FROM dbo.ArticleTopWidget w
INNER JOIN dbo.CorePageVersion cpv ON w.CoreArticleVersionID = cpv.CoreVersionID
INNER JOIN dbo.CorePage cp ON cpv.CorePageID = cp.CoreID
WHERE cp.CoreRemoteSite = 'AmeliaCore'
) AS tmp1
INNER JOIN ( SELECT cpv.CorePageID ,
MAX(cpv.CoreVersionID) AS LastVersionID
FROM dbo.CorePageVersion cpv
WHERE cpv.CoreStatus=4
GROUP BY cpv.CorePageID
) AS tmp2 ON tmp1.CoreID = tmp2.CorePageID AND tmp1.CoreVersionID = tmp2.LastVersionID
) AS T
WHERE T.WidgetTypeName IN ('[Widget] Shop image gallery widget','[Widget] Factbox','[Widget] Html','[Widget] Quick poll widget','[Widget] Quiz widget')
--IS NOT NULL --AND T.CorePageID < 110683 --(T.CorePageID>=113351 AND T.CorePageID<=110683000)
ORDER BY T.CorePageID

Write a comment

To post comments, please log in or register.