Dynamicweb CMS sql scripts

DynamicwebCMSsqlScripts.sql

-- Dynamicweb CMS sql scripts
-- By Sten Hougaard, [email protected]
FOR XML AUTO

-- Opdateret

---------------------------------------------------------e------------------------------
-- Accessuser/page
---------------------------------------------------------------------------------------
-- Find pages modified by non-admins on given areaid
p
SELECT page.PageID,
       AccessUserUserName,
       AccessUserName,
       AccessUserEmail
FROM page
JOIN accessuser ON page.PageUserEdit = accessuser.AccessUserID
WHERE AccessUserType>1
    AND page.pageareaid = 26
    SELECT AccessUserID,
       AccessUserUserName,
       AccessUserName,
       AccessUserEmail
    FROM accessuser

----------
-- Enable alle users
----------

UPDATE accessuser
SET AccessUserActive = 1 WHERE AccessUserActive = 0
AND AccessUserType = 5

---------------------------------------------------------------------------------------
-- Page
---------------------------------------------------------------------------------------
-- Find pageids on area where ecomnavigation is active

SELECT pageid,
       PageNavigation_UseEcomGroups,
       PageNavigationParentType,
       PageNavigationGroupSelector,
       PageNavigationMaxLevels,
       PageNavigationProductPage,
       PageNavigationShopSelector
    FROM page WHERE pageareaid = 26
    AND PageNavigation_UseEcomGroups != 'False'

----------------
---  ECOM FILTER
----------------
select * from EcomGroupFilterSetting 

update EcomGroupFilterSetting 
set EcomGroupFilterSettingVisibility  = 'Prohibit'  
where EcomGroupFilterSettingGroupID  in ('SHOP1_FJERNBETJE', 'SHOP1_MIKROFON','SHOP1_KIKKERT','SHOP1_REMME', 'SHOP1_ANDET', 'SHOP1_PRINTER', 'SHOP1_FORBRUGSTO' )

select * from EcomFilterDefinition 
order by EcomFilterDefinitionName 



------
-- Direct path
------
-- Find urlpaths

    SELECT [UrlPathID] ,
       [UrlPathPath] ,
       [UrlPathActive] ,
       [UrlPathAreaID]
    FROM [danboDW8].[dbo].[UrlPath] WHERE UrlPathPath = 'kontakt'
ORDER BY UrlPathAreaID

-- Disable urlpaths

UPDATE [danboDW8].[dbo].[UrlPath]
SET [UrlPathActive] = 0
WHERE UrlPathPath = 'kontakt'

-- Find order via ordid

    SELECT *
    FROM ecomorders WHERE OrderID = 'ORDER509'
    
-- find orders which has been completed within the last 14 days
select OrderID as ordrenummer,
OrderCustomerName as kundenavn,
OrderCustomerAddress as  kundeadresse,
OrderCustomerZip as postnr,
OrderCustomerCity as kundeby,
OrderDate as ordredato,
ecomOrderStates.OrderStateName as ordrestatus,
concat('http://yourdoma.in/pageWithCartV2.aspx?CompletedOrderId=',OrderID,'&CompletedOrderSecret=',OrderSecret) as OrderLink
from ecomorders
join ecomOrderStates on ecomorders.OrderStateID = ecomOrderStates.OrderStateID
where DATEDIFF(day,orderdate,GETDATE()) <= 14
and OrderComplete = 1

-- Find order via customer name

SELECT VikingOrderNo, OrderID, OrderPriceWithVAT, OrderCustomerEmail, OrderCustomerName, OrderComplete, OrderCustomerAddress, OrderGatewayPaymentStatus, OrderGatewayResult, OrderGatewayTransactionProblems, OrderPaymentMethod FROM ecomorders
where OrderCustomerName like 'sonja%'

-- Find number of products with comment
SELECT count(productname) from COMMENT
join ecomproducts on CommentItemID = ecomproducts.productid
where CommentItemType = 'ecomProduct'
and ecomproducts.productactive = 1


-- Find salesdiscount

    SELECT *
    FROM EcomSalesDiscount WHERE SalesDiscountProductsAndGroups !='' --where SalesDiscountName = 'DUMMY'

-- Find groups which a product is related to
SELECT GroupProductRelationGroupID, *  FROM ecomGroupProductrelation
where groupproductrelationproductid = 'PROD504'

-- Find groups used on a given LANGID and SHOPID
SELECT groupid, groupname FROM EcomGroups
where Groupid in (
       SELECT distinct groupid FROM EcomGroups WHERE GroupID in 
              (SELECT ShopGroupGroupID FROM EcomShopGrouprelation WHERE ShopGroupShopID = 'SHOP1')
       AND GroupLanguageID = 'LANG1')", shopID, langID)
       

---------------------------------------------------------------------------------------
-- GENERIC  GENERIC GENERIC GENERIC GENERIC GENERIC GENERIC GENERIC GENERIC GENERIC
---------------------------------------------------------------------------------------
-- Find tables in Dynamicweb CMS

SELECT name
FROM SysObjects WHERE xtype = 'U'
and name like '%filter%'
ORDER BY name

-- find defined items
SELECT name
FROM SysObjects WHERE xtype = 'U'
and name like 'ItemType_%'
ORDER BY name

-- col info about item
SELECT sys.columns.name,
       sys.types.[name] as coltype, sys.columns.[max_length],
       sys.columns.is_nullable
FROM sys.columns
JOIN sys.types ON sys.columns.[system_type_id] = sys.types.[system_type_id]
WHERE object_id = OBJECT_ID('ItemType_Literature')
and sys.types.[name] != 'sysname'
ORDER BY sys.columns.name

-- Create a list of distinct values and their count
SELECT Region, count(*) AS count
FROM itemtype_story
WHERE Region is not null
GROUP BY Region



SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME LIKE 'news%'
ORDER BY [TABLE_NAME]

-- Get columns info

SELECT sys.columns.name,
       sys.types.[name] as coltype, sys.columns.[max_length],
       sys.columns.is_nullable
FROM sys.columns
JOIN sys.types ON sys.columns.[system_type_id] = sys.types.[system_type_id]
WHERE object_id = OBJECT_ID('ecomorders')
and sys.types.[name] != 'sysname'
ORDER BY sys.columns.name


-- Find information about table

SELECT name,
       *
FROM sys.columns
WHERE object_id = OBJECT_ID('AccessUser')

-- Find types of sql

    SELECT *,name, PRECISION, SCALE, max_length
    FROM sys.types
ORDER BY [system_type_id]

-----
-- Paragraph
--

SELECT *
FROM paragraph
WHERE CAST(ParagraphModuleSystemName AS VARCHAR) LIKE '%NewsV2%'

-- FIND PARAGRAPHS WITH MODULE NewsV2


-- Find paragraph module setting from paragraphs which are below a given pageid

    SELECT ParagraphModuleSettings,
       *
    FROM paragraph WHERE paragraphpageid IN
        (SELECT pageid
         FROM page
         WHERE pageparentpageid = 3164)
    AND cast(ParagraphModuleSettings AS varchar(3000)) != ''


-- Find modules used on paragraph
SELECT DISTINCT ParagraphModuleSystemName
FROM paragraph WHERE ParagraphModuleSystemName != ''
ORDER BY ParagraphModuleSystemName


-- Find paragraph using a given module
SELECT * FROM paragraph
WHERE ParagraphModuleSystemName 
= 'NewsV2'

-- Find module settings

SELECT ParagraphModuleSettings
FROM paragraph
WHERE ParagraphModuleSystemName = 'NewsV2'
    FOR XML PATH

---------------------------------------------------------------------------------------
-- STYLESHEETS STYLESHEETS STYLESHEETS STYLESHEETS STYLESHEETS STYLESHEETS STYLESHEETS
---------------------------------------------------------------------------------------
-- STYLESHEETS: Find names of used stylesheets on soloution

SELECT StylesheetStylesheetNodename,
       StylesheetStylesheetID
    FROM StylesheetStylesheet WHERE StylesheetStylesheetID IN
        (SELECT DISTINCT PageStylesheet AS StylesheetStylesheetID
         FROM page)
ORDER BY StylesheetStylesheetNodename

-- STYLESHEETS: Find names of used stylesheets on AREA

SELECT StylesheetStylesheetNodename,
       StylesheetStylesheetID
FROM StylesheetStylesheet
WHERE StylesheetStylesheetID IN
        (SELECT DISTINCT PageStylesheet AS StylesheetStylesheetID
         FROM page
         WHERE PageAreaID = 52
         OR PageAreaID = 51)
ORDER BY StylesheetStylesheetNodename

-- STYLESHEETS: Find a page from specified areaid where page uses a given stylesheetname

SELECT top(1) StylesheetStylesheet.StylesheetStylesheetNodename,
       PageMenuText,
       PageID
FROM page
LEFT JOIN StylesheetStylesheet ON page.PageStylesheet = StylesheetStylesheet.StylesheetStylesheetID
WHERE StylesheetStylesheet.StylesheetStylesheetNodename LIKE 'STYLESHEETNAME%'
    AND (PageAreaID = 52
         OR PageAreaID = 51)


-- Denne sql viser navne på de templates der er i brug på aktive templates på en løsning

SELECT TemplateIsDefault,
       TemplateName,
       TemplateFile,
       TemplateDescription
FROM TEMPLATE WHERE TemplateId IN
    (SELECT DISTINCT ParagraphTemplateID AS TemplateId
     FROM paragraph
     WHERE ParagraphShowParagraph = 1)
ORDER BY TemplateName

--- A list with count of used templates on active paragraphs
SELECT count(ParagraphTemplate) as used, ParagraphTemplate FROM paragraph
where ParagraphTemplate  in (
  select distinct ParagraphTemplate from Paragraph
  where ParagraphTemplate != ''
  and ParagraphShowParagraph = 1
  )
and ParagraphShowParagraph = 1
group by ParagraphTemplate
order by used desc

--- A list with count of used templates on active pages
SELECT count(PageLayout) as used, PageLayout FROM Page
where PageLayout  in (
  select distinct PageLayout from Page
  where PageLayout != ''
  and PageActive = 1
  )
group by PageLayout
order by used desc

--- A list with count of used modules on active paragraphs
SELECT count(ParagraphModuleSystemName) as used,module.ModuleName FROM paragraph
join module on module.ModuleSystemName = paragraph.ParagraphModuleSystemName
where ParagraphModuleSystemName  in (
  select distinct ParagraphModuleSystemName from paragraph
  where ParagraphModuleSystemName != ''
  and ParagraphShowParagraph  = 1
  )
group by module.ModuleName
order by used desc



SELECT PageNoindexNofollow,
       PageRobots404,
       *
FROM page
WHERE PageAreaID = 1
    UPDATE page
    SET PageNoindexNofollow = 1,
        PageRobots404 = 1 WHERE PageAreaID = 1
    UPDATE page
    SET PageNoindexNofollow = 1,
        PageRobots404 = 1 WHERE PageAreaID = 1

--- Which layouts are used on soloution
SELECT DISTINCT PageLayout
FROM page 
where Pagelayout is not null and PageLayout != ''
order by PageLayout

-- Pages using a specified layout
SELECT * FROM page 
where Pagelayout = 'service_v1.html'



--- Datalist ---
--- Update connection db name after database copied from one server to another --

    UPDATE DMViewSetting
    SET ViewSettingConnectionDBName = 'liveServerDatabaseName'
    WHERE ViewSettingConnectionDBName = 'developmentServerDatabaseName'
    
-- Get number of orders in each shop
SELECT OrderShopId, count(*) AS orders
FROM ecomorders
where OrderShopID != ''
GROUP BY OrderShopID 

-- get active products with variant
SELECT top(10) * FROM ecomproducts
where ProductVariantID  != ''
and productactive = 1

-- Get products which have a give product related to them
select top(10) ProductRelatedProductID  from EcomProductsRelated 
where ProductRelatedProductRelID  = '108648'

-- get productvariants
select productid, ProductVariantID,productlanguageid , productname from ecomproducts
where productid = '108817'
and productlanguageid = 'LANG1'
and productactive = 1


-- Defined product fields
select * from EcomProductField 

-- Defined options on product fields (list type)
select * from EcomFieldOption 

-- Defined translations for options on product fields (list type)
select * from EcomFieldOptionTranslation 

Her er en række nyttige Dynamicweb specifikke SQL queries