Article: AN0002366Updated: 04.11.2018
The Editor is using a page for an effective content management. Let`s have a look how we created this page.
This page looks like this.
Page layout is very simple. We have selected option Two columns: Left wide, right thin. The page should be accessible only to users with Editor role. Therefore we set the role for page display.
We can see three webparts in the page. Two of them (webpart Menu and webpart Grid) are inserted into the left, wide zone. The third one (webpart Percentage value display) is inserted into the right, thin zone.
We will define all the links as a fast access to various classes in the webpartu Menu. The definition looks like this:
cat= |text=
page=vcd.article|typeUrl=l|text=en-US::Version Controlled Documentation~cs-CZ::Verzovaná dokumentace|img=ImagesData/Images_32_32/Type_1/Blue/addressbook.png
classdef=vcd.topic|typeUrl=l|text=en-US::Topic~de-DE::Thema~cs-CZ::Téma|img=ImagesData/Images_32_32/Type_1/Blue/bookmark.png
cat= |text=
classdef=vcd.article|typeUrl=l|text=en-US::Article~de-DE::Artikel~cs-CZ::Článek|img=ImagesData/Images_32_32/Type_1/Blue/edit.png
classdef=vcd.article_text|typeUrl=l|text=en-US::Article text~de-DE::Artikeltext~cs-CZ::Text článku|img=ImagesData/Images_32_32/Type_1/Blue/copy-item.png
cat= |text=
classdef=vcd.article|typeUrl=l|text=en-US::Tag~de-DE::Tag~cs-CZ::Tag|img=ImagesData/Images_32_32/Type_1/Blue/tag.png
classdef=vcd.article|typeUrl=l|text=en-US::Article display count~cs-CZ::Počet zobrazení článků|img=ImagesData/Images_32_32/Type_1/Blue/bar-chart.png
cat= |text=
classdef=vcd.article|typeUrl=l|text=en-US::Article evaluation~de-DE::Artikelbewertung~cs-CZ::Hodnocení článku|img=ImagesData/Images_32_32/Type_1/Blue/star.png
classdef=vcd.wrong_search|typeUrl=l|text=en-US::Erroneous searches~de-DE::Falsche Suchen~cs-CZ::Chybná hledání|img=ImagesData/Images_32_32/Type_1/Blue/lookup.png
cat= |text=
classdef=vcd.language|typeUrl=l|en-US::Language~de-DE::Sprache~cs-CZ::Jazyk|img=ImagesData/Images_32_32/Type_1/Blue/chat-.png
classdef=vcd.version|typeUrl=l|text=en-US::Version~de-DE::Version~cs-CZ::Verze|img=ImagesData/Images_32_32/Type_1/Blue/push-pin.png
We want to display all the articles that require editor`s attention in the webpart Grid. There are several reasons for highlighting an article. We will use a query that will select records and states a reason for each of them. The query will consist of several SELECT clauses connected by UNION clause. Each block in the below example represents one reason. Each block selects certain records and all the records are grouped by the UNION clause into a single data set. Each block below has an explanatory comment saying which particular records are selected. We are selecting not only data that we want to display in the webpart Grid but also another data. The reason for that is that we will use this query also for the third webpart that will display a summary data - count of particular types of records that require our attention. The query contains a link to the article, localized description of the reason for highlighting, link to the icon representing the reason and colour for the reason.
//Records marked in the article in the checkbox Check content
SELECT ar.id AS article___article, 'en-US::Check content~de-DE::Inhalt prüfen~cs-CZ::Zkontrolovat obsah' AS Action,
'ImagesData/Icons/Common/package.png' AS Icon, '#FF0000' AS Colour,CAST(10 AS Integer) AS [Order], CONVERT(INT, 0xFF0000) colour2
FROM {{:class.article:}} ar
WHERE ar.content_check = 1 AND ar.deleted IS NULL
UNION
//Records marked in the article in the checkbox Check grammar
SELECT ar.id AS article___article, 'en-US::Grammar check~de-DE::Grammatik prüfen ~cs-CZ::Zkontrolovat gramatiku' AS Action,
'ImagesData/Icons/Common/text_dropcaps.png' AS Icon, '#FF6600' AS Colour,CAST(20 AS Integer) AS [Order], CONVERT(INT, 0xFF6600) colour2
FROM {{:class.article:}} ar
LEFT JOIN {{:class.article_text:}} art ON ar.id = art.article
WHERE art.grammar_check = 1 AND ar.deleted IS NULL
UNION
//Records marked in the article text in the checkbox Translate
SELECT ar.id AS article___article, 'en-US::Translate~de-DE::Übersetzten~cs-CZ::Přeložit' AS Action,
'ImagesData/Icons/Common/table_relationship.png' AS Icon, '#FFCC00' AS Colour, CAST(30 AS Integer) AS [Order],CONVERT(INT, 0xFFCC00) colour2
FROM {{:class.article:}} ar
LEFT JOIN {{:class.article_text:}} art ON ar.id = art.article
WHERE art.translate = 1 AND ar.deleted IS NULL
UNION
//Articles without any texts
SELECT ar.id AS article___article, 'en-US::Missing texts~de-DE::Fehlende Texte~cs-CZ::Chybějící texty' AS Action,
'ImagesData/Icons/Common/table_row_delete.png' AS Icon, '#3333FF' AS Colour,CAST(40 AS Integer) AS [Order],CONVERT(INT, 0x3333FF) colour2
FROM {{:class.article:}} ar
LEFT JOIN {{:class.article_text:}} art ON ar.id = art.article
WHERE art.id IS NULL AND ar.deleted IS NULL
UNION
//Articles without texts in obligatory languages for the last version - the obligatory languages are defined directly in SQL
SELECT qsub2.article___article,
'en-US::Texts missing in some mandatory languages~de-DE::Fehlende Texte in verpflichtenden Sprachen~cs-CZ::Chybějící texty v povinných jazycích' AS Action,
'ImagesData/Icons/Common/table_key.png' AS Icon, '#339900' AS Colour, CAST(50 AS Integer) AS [Order],CONVERT(INT, 0x339900) colour2
FROM (
SELECT qsub.article___article, CASE WHEN lan.code = 'en-US' OR lan.code = 'cs-CZ' THEN 1 END AS def_lan
FROM (
SELECT ar.id AS article___article, MAX (ver.[order]) AS Last_version_order
FROM {{:class.article:}} ar
LEFT JOIN {{:class.article_text:}} art ON ar.id = art.article
LEFT JOIN {{:class.version:}} ver ON ver.id = art.from_version
WHERE ar.deleted IS NULL
GROUP BY ar.id) qsub
LEFT JOIN {{:class.article_text:}} art2 ON qsub.article___article = art2.article
LEFT JOIN {{:class.version:}} ver2 ON ver2.[order] = qsub.Last_version_order
LEFT JOIN {{:class.language:}} lan ON lan.id = art2.language
WHERE art2.deleted IS NULL AND art2.from_version = ver2.id) qsub2
GROUP BY qsub2.article___article
HAVING Sum (qsub2.def_lan) < 2
UNION
//Articles that do not have a parent article or a topic
SELECT ar.id AS article___article, 'en-US::Missing parent~de-DE::Fehlender Elternartikel~cs-CZ::Chybějící nadřizený článek' AS Action,
'ImagesData/Icons/Common/arrow_up.png' AS Icon, '#FF33CC' AS Colour, CAST(60 AS Integer) AS [Order],CONVERT(INT, 0xFF33CC) colour2
FROM {{:class.article:}} ar
WHERE ar.parent_article IS NULL AND ar.topic IS NULL AND ar.deleted IS NULL
UNION
//Articles that are not selected as active
SELECT ar.id AS article___article, 'en-US::Inactive article~de-DE::Inaktiver Artikel~cs-CZ::Neaktivní článek' AS Action,
'ImagesData/Icons/Common/disconnect.png' AS Icon,'#66CCFF' AS Colour, CAST(70 AS Integer) AS [Order],CONVERT(INT, 0x66CCFF) colour2
FROM {{:class.article:}} ar
WHERE ar.active = 0 AND ar.deleted IS NULL
The query data look like this:
The above stated query will be used also for the last webpart. We will first create a grouping query on it.
SELECT action, colour2, [order], COUNT (action) AS article_count
FROM {{:query.articles_to_follow_up:}} atfu
GROUP BY action, colour2, [order]
The resulting data look like this:
We will configure the webpart Percentage value display in the following way:
Webpart Percentage value display shows the same record counts as the detailed overview of the webpart Grid.