1. Case study: Version Controlled Documentation - Support for Content management

    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

    cat= |text=
    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::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=

    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


    //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


    //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


    //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


    //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


    //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


    //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.