USE POLARIS

-- Provides a list of all label settings, by the domain level the label is configured at
-- (mainly at a branch level, with customizations recorded at the workstation level)

-- Enter a value below to search for part of a label name
DECLARE @LabelName varchar(max) = ''

select
    lt.DisplayName,
    --lcd.LabelTypeID, -- ID for the specific label
    --lcd.CtxID, -- ID for the iteration of the label, i.e. configired for a specific branch or workstation
    --lcd.DomainID,
    d.Domain, -- the level at which this version of the label is owned, whether branch, workstation, etc.
    CASE WHEN lcd.DomainID = 4 THEN o.name ELSE NULL END Branch,
    CASE WHEN lcd.DomainID = 16 THEN ws.ComputerName ELSE NULL END ComputerName,
    --up.PolarisUserID,
    lcd.Enabled,
    lcd.PageWidth,
    lcd.PageHeight,
    lcd.PageTopMargin,
    lcd.PageLeftMargin,
    lcd.TextAlignment,
    font.LabelWidth,
    font.LabelHeight,
    font.LabelIndent,
    font.VerticalGap,
    font.HorizontalGap,
    ct.ContentTypeName,
    font.FontName,
    font.FontSize,
    font.IsBold,
    font.IsItalic,
    font.IsStrikeThrough,
    font.IsUnderLine,
    font.FontColor
from polaris.LabelConfigDefs lcd (nolock)
inner join polaris.LabelConfigFontDefs font (nolock)
    on lcd.CtxID = font.CtxID
        and lcd.LabelTypeID = font.LabelTypeID
inner join polaris.labeltypes lt (nolock)
    on lcd.LabelTypeID = lt.LabelTypeID
INNER JOIN Polaris.LabelContentTypes ct (nolock)
    on font.ContentTypeID = ct.ContentTypeID
LEFT join polaris.Workstations ws (nolock)
    on ws.WorkstationID = lcd.OwnerID
LEFT JOIN Polaris.Organizations o (nolock)
    on lcd.OwnerID = o.OrganizationID
LEFT JOIN Polaris.Organizations o2 (nolock)
    on ws.OrganizationID = o2.OrganizationID
LEFT JOIN Polaris.Domains d (nolock)
    on lcd.DomainID = d.DomainID
LEFT join polaris.userspppp up (nolock)
    on up.[Value] = lcd.CtxID and up.AttrID = 1769
where lt.DisplayName like '%' + @LabelName + '%'  -- label name

-- labels are mainly configured at the branch and workstation levels, domainIDs 4 and 16 respectively
-- other domains exist, as well, but I don't believe these are actively used by staff
and lcd.DomainID in (16, 4)

group by 
    lt.DisplayName,
    --lcd.LabelTypeID,
    --lcd.CtxID,
    --lcd.DomainID,
    d.Domain,
    CASE WHEN lcd.DomainID = 16 THEN ws.ComputerName ELSE NULL END,
    CASE WHEN lcd.DomainID = 4 THEN o.name ELSE NULL END,
    --up.PolarisUserID,
    lcd.Enabled,
    lcd.PageWidth,
    lcd.PageHeight,
    lcd.PageTopMargin,
    lcd.PageLeftMargin,
    lcd.TextAlignment,
    font.LabelWidth,
    font.LabelHeight,
    font.LabelIndent,
    font.VerticalGap,
    font.HorizontalGap,
    ct.ContentTypeName,
    font.FontName,
    font.FontSize,
    font.IsBold,
    font.IsItalic,
    font.IsStrikeThrough,
    font.IsUnderLine,
    font.FontColor
order by 
    lt.DisplayName,
    d.Domain,
    --lcd.DomainID,
    CASE WHEN lcd.DomainID = 4 THEN o.name ELSE NULL END,
    CASE WHEN lcd.DomainID = 16 THEN ws.ComputerName ELSE NULL END,
    --lcd.LabelTypeID,
    --lcd.CtxID,
    --up.PolarisUserID,
    lcd.Enabled,
    lcd.PageHeight,
    lcd.PageWidth,
    lcd.PageTopMargin,
    lcd.PageLeftMargin,
    lcd.TextAlignment,
    font.FontName,
    font.FontSize,
    font.IsBold
