Sudan Memory Translations

Introduction
This statistics plugin enables statistical recording of the activity of translators and editors who edit specific metadata fields within the METS file. In particular, the translation work in the metadata fields Title (Arabic), Title (English), Description (English) and Description (Arabic) is taken into account.
Overview
Details
Text
Identifier
intranda_statistics_sudan_memory_activity_by_user
Source code
​https://github.com/intranda/goobi-plugin-statistics-sudan-memory​
Licence
GPL 2.0 or newer
Compatibility
Goobi workflow 20.10
Documentation date
03.01.2021
Installation
To install the plugin, the following two files must be installed:
1
/opt/digiverso/goobi/plugins/statistics/plugin_intranda_statistics_sudan.jar
2
/opt/digiverso/goobi/plugins/GUI/plugin_intranda_statistics_sudan-GUI.jar
Copied!
In addition, the following function must be created within the database:
1
DROP FUNCTION IF EXISTS wordcount;
2
​
3
    DELIMITER $
4
    CREATE FUNCTION wordcount(str TEXT CHARSET utf8mb4)
5
            RETURNS INT
6
            DETERMINISTIC
7
            SQL SECURITY INVOKER
8
            NO SQL
9
       BEGIN
10
         DECLARE wordCnt, idx, maxIdx INT DEFAULT 0;
11
         DECLARE currChar, prevChar BOOL DEFAULT 0;
12
         SET maxIdx=char_length(str);
13
         WHILE idx < maxIdx DO
14
             SET currChar=SUBSTRING(str, idx, 1) RLIKE '[[:alnum:]]';
15
             IF NOT prevChar AND currChar THEN
16
                 SET wordCnt=wordCnt+1;
17
             END IF;
18
             SET prevChar=currChar;
19
             SET idx=idx+1;
20
         END WHILE;
21
         RETURN wordCnt;
22
       END
23
     $
24
     DELIMITER ;
Copied!
A UTF8-encoded text can be passed to this function. The text is checked character by character. If the current character is an alnumeric character (letters, numbers, full stop, comma, letters with diacritics, brackets) but the previous character is not (nothing, space, newline, tab), a new word starts at this point and the word counter is incremented. At the end, the word counter is returned.
Configuration of the plugin
To use this plugin, the user must have the correct role authorisation.
Without correct authorisation, the plugin cannot be used
Therefore, please assign the role view_translation_activity to the group.
Correctly assigned role für the users
Afterwards, the menu item Translation and Editing Activity can be selected in the section Management.
Calling up the plugin in the menu
How to use the plugin
In order to limit the period of the evaluation, the two fields Period from and Period to can be used for the start date and end date. A date in the form YYYY-MM-DD can be entered here. Both entries are optional. If the start date is not filled in, the date on which the first step was completed applies. If the end date is missing, the current date is used.
Selection of the period
In the Unit field, you define the time periods in which the evaluation is to be summarised. Here you can choose from the values days, months, quarters or years.
After specifying the required information, two different evaluations can be generated by this plugin:
Evaluation: Overview
The evaluation Overview lists for each period within the start and end date which user has processed how many work steps Translation of Arabic content to English or Translation of English content to Arabic. It also shows how many words were entered in the fields Title (Arabic), Title (English), Description (English) and Description (Arabic) in these steps.
Evaluation: Detailed View
The Detailed View lists each workflow step Translation of Arabic content to English or Translation of English content to Arabic that was completed within the specified start and end date. For each step, the user, the associated process, and the content and number of words from the four fields Title (Arabic), Title (English), Description (English) and Description (Arabic) are also displayed.
Display of results with possibility for download
The two evaluations can also be downloaded as Excel files.
Further technical information
The following are some SQL statements that may be useful for working with the data in the context of this plugin.
SQL query via a general overview:
1
    SELECT
2
    DATE_FORMAT(s.BearbeitungsEnde, '%Y-%m') AS plugin_statistics_sudan_timeRange,
3
    WORDCOUNT(GROUP_CONCAT(m1.value SEPARATOR ' ')) AS plugin_statistics_sudan_titleCount,
4
    WORDCOUNT(GROUP_CONCAT(m2.value SEPARATOR ' ')) AS plugin_statistics_sudan_titlearabicCount,
5
    WORDCOUNT(GROUP_CONCAT(m3.value SEPARATOR ' ')) AS plugin_statistics_sudan_descriptionCount,
6
    WORDCOUNT(GROUP_CONCAT(m4.value SEPARATOR ' ')) AS plugin_statistics_sudan_descriptionarabicCount,
7
    COUNT(s.Titel) AS plugin_statistics_sudan_workflowTitleCount,
8
    CONCAT(u.Nachname, ', ', u.Vorname) AS plugin_statistics_sudan_userName
9
    FROM
10
    metadata m1
11
        JOIN
12
    metadata m2 ON m1.processid = m2.processid
13
        JOIN
14
    metadata m3 ON m1.processid = m3.processid
15
        JOIN
16
    metadata m4 ON m1.processid = m4.processid
17
        JOIN
18
    schritte s ON m1.processid = s.ProzesseID
19
        LEFT JOIN
20
    benutzer u ON s.BearbeitungsBenutzerID = u.BenutzerID
21
    WHERE
22
    m1.name = 'TitleDocMain'
23
        AND m2.name = 'TitleDocMainArabic'
24
        AND m3.name = 'ContentDescription'
25
        AND m4.name = 'ContentDescriptionArabic'
26
        AND s.typMetadaten = TRUE
27
        AND s.Bearbeitungsstatus = 3
28
        AND s.titel like '%ranslat%'
29
        AND s.BearbeitungsEnde BETWEEN '2019-01-01' AND '2020-12-31'
30
    GROUP BY plugin_statistics_sudan_timeRange, plugin_statistics_sudan_userName;
Copied!
SQL query for a detailed report:
1
    SELECT
2
    m1.processid,
3
    m1.value AS plugin_statistics_sudan_title,
4
    WORDCOUNT(m1.value) AS plugin_statistics_sudan_titleCount,
5
    m2.value AS plugin_statistics_sudan_titlearabic,
6
    WORDCOUNT(m2.value) AS plugin_statistics_sudan_titlearabicCount,
7
    m3.value AS plugin_statistics_sudan_description,
8
    WORDCOUNT(m3.value) AS plugin_statistics_sudan_descriptionCount,
9
    m4.value AS plugin_statistics_sudan_descriptionarabic,
10
    WORDCOUNT(m4.value) AS plugin_statistics_sudan_descriptionarabicCount,
11
    s.Titel AS plugin_statistics_sudan_workflowTitle,
12
    p.Titel AS plugin_statistics_sudan_processTitle,
13
    CONCAT(u.Nachname, ', ', u.Vorname) AS plugin_statistics_sudan_userName
14
    FROM
15
    metadata m1
16
        JOIN
17
    metadata m2 ON m1.processid = m2.processid
18
        JOIN
19
    metadata m3 ON m1.processid = m3.processid
20
        JOIN
21
    metadata m4 ON m1.processid = m4.processid
22
        JOIN
23
    schritte s ON m1.processid = s.ProzesseID
24
        LEFT JOIN
25
    prozesse p ON s.ProzesseID = p.ProzesseID
26
        LEFT JOIN
27
    benutzer u ON s.BearbeitungsBenutzerID = u.BenutzerID
28
    WHERE
29
    m1.name = 'TitleDocMain'
30
        AND m2.name = 'TitleDocMainArabic'
31
        AND m3.name = 'ContentDescription'
32
        AND m4.name = 'ContentDescriptionArabic'
33
        AND s.typMetadaten = TRUE
34
        AND s.titel like '%ranslat%'
35
        AND s.Bearbeitungsstatus = 3
36
        AND s.BearbeitungsEnde BETWEEN '2019-01-01' AND '2020-12-31';
Copied!