Getting the page ready for the live event

[xkon]

Greetings everyone!

We have talked in the past for our approach during the live event and the Data that we want to show and how we are going to show them.

Since it's getting a bit hard to find the right time for a meeting I'm opening this ticket so we can all contribute here our thoughts and make a final decision so we can start the ui & dev process of it. As I always say I prefer to have everything ready and waiting than running last moment trying to fix them.

To start with, there were some pretty cool ideas from @mapk with 'movable boxes' done with p5.js the simplicity of his approach was really interesting and would be making it super fast to load I guess. @mapk if you can please provide us with a temp link again so everybody can have a look.

We now as well have @audrasjb on board with us who knows his way around Maps ( he made the local events map happen ​http://wptranslationday.org/local-events/ ) as there was a thought of having a Map with popup info boxes to show all the live data which would be hidden on mobile / tablets and replaced by a simple info box to avoid a laggy page.

@ocean90 gave some information on what we can already gather. If anybody has that list handy please give it here with the api end-points as well or any way we can access them to at least make some tests and see what fits us better on refresh rates etc on development side.

Any extra Data that we might want to use have to be also stated here so @ocean90 can verify if there's a possibility to make that happen.

We are already in September so let's make this happen you awesome team !

[xkon]

Input from @zetaraffix since she's traveling atm given on slack on #polyglots-events :

Hey guys, quickly (this I've written before)

The data I would like to show (still waiting to know if possible):

- people logged in Glot0Press during event (live data)
- locale is automatic BUT 
- location: me would be very happy if we could come up 
with a geo-localizing thing (I was wondering if one could 
say tweet a specific hashtag with geo refs that we could pick up)
- strings being translated (live)
- meetup information could possibly be merged.

Once we have clear idea of what can be accessed data wise it's easier to work on design.

I see a map with visual information (size of dot, for example) AND 
interactive information  (pop up with details)

Also some large numerical information to be displayed above 
or below (like users logged/translating and strings being translated)

[audrasjb]

So, after our last weekly chat, here are the data we would like to show:

1. Total number of strings modified/added per project since UTC 00:00
2. Total number of strings modified/added per locale since UTC 00:00
3. Total number of current/waiting/rejected strings since UTC 00:00
4. Current Total translators in the system
5. Current Total number of GTE per locale
6. Current Total number of PTE per locale
7. Latest 100 strings modified/added (source string, translated strings, locale, string permalink, contributor)

Data resfreshing in a 10min interval would be fine.

FYI: what we are doing on our side:
– Create a table with all locales and link them to a place on the map (country, region) (not sure it will be used, but let's do it anyway)
– Check our current solution to display data on "locale boxes".
– Create a map based on the table in item 1 and show localized data on it.
– Check cron access instead of wp_cron, if it's possible.

So, here is some code (thanks @casiepa for the archives):

1. Total number of strings modified/added per project since UTC 00:00

<?php
/* The below script is just a guideline used in previous WPTranslationDays */
$day = '2017-09-30';
$results = $wpdb->get_results("
SELECT
    COUNT( * ) as 'total`,
    p.name,
    p.parent_project_id
FROM translate_translations t
JOIN translate_translation_sets ts ON ts.id = t.translation_set_id
JOIN translate_projects p ON p.id = ts.project_id
WHERE
    (user_id <> 0 OR user_id IS NOT NULL) AND
    date_added >= '$day 00:00:00' AND date_added < '$day 24:00:00'
GROUP BY p.id
" );

echo "Name\tTotal\n";
foreach ($results as $result ) {
    $project_name = $result->name;
    $parent_project_id = $result->parent_project_id;
    while ( $parent_project_id ) {
        $parent_project = GP::$project->get( $parent_project_id );
        $parent_project_id = $parent_project->parent_project_id;
        $project_name = "{$parent_project->name} - {$project_name}";
    }
    echo $project_name;
    echo "\t" . $result->total; // Output total number of string modified/added for this project for this day (UTC 00:00)
    echo "\n";
}
?>

2. Total number of strings modified/added per locale since UTC 00:00

<?php
/* The below script is just a guideline used in previous WPTranslationDays */
$day = '2017-09-30';
$results = $wpdb->get_results("
SELECT
    COUNT( * ) as `total`,
    ts.name, ts.locale, ts.slug
FROM translate_translations t
JOIN translate_translation_sets ts ON ts.id = t.translation_set_id
WHERE
    (user_id <> 0 OR user_id IS NOT NULL) AND
    date_added >= '$day 00:00:00' AND date_added < '$day 24:00:00'
GROUP BY ts.locale, ts.slug
" );

echo "Locale\tSlug\tTotal\n";
foreach ($results as $result ) {
    echo $result->locale;
    echo "\t" . $result->locale;
    echo "\t" . $result->slug;
    echo "\t" . $result->total; // Output total number of string modified/added for this locale for this day (UTC 00:00)
    echo "\n";
}
?>

3. Total number of current/waiting/rejected strings since UTC 00:00

<?php
/* The below script is just a guideline used in previous WPTranslationDays */
echo "Total\tCurrent\tWaiting\tRejected\tUsers\n";
for ( $i = 0; $i <= 23; $i++ ) {
    $hour_start = str_pad( $i, 2, '0', STR_PAD_LEFT );
    $hour_end   = str_pad( $i + 1, 2, '0', STR_PAD_LEFT );

    $results = $wpdb->get_row("
    SELECT
        COUNT( * ) as `total_count`,
        COUNT( CASE WHEN `status` = 'current' THEN `status` END ) AS `current_count`,
        COUNT( CASE WHEN `status` = 'waiting' THEN `status` END ) AS `waiting_count`,
        COUNT( CASE WHEN `status` = 'rejected' THEN `status` END ) AS `rejected_count`,
        COUNT( DISTINCT( user_id ) )
    FROM translate_translations
    WHERE
        (user_id <> 0 OR user_id IS NOT NULL) AND
        date_added >= '$day $hour_start:00:00' AND date_added < '$day $hour_end:00:00'
    ", ARRAY_N );

    echo implode( "\t", $results ); // Output total number of current/waiting/rejected strings for this day (UTC 00:00)
    echo "\n";
}
?>

4. Current Total translators in the system

<?php
/* The below script is just a guideline used in previous WPTranslationDays */
$total_users = $wpdb->get_var("
    SELECT COUNT( DISTINCT( user_id ) )
    FROM translate_translations
    WHERE
        (user_id <> 0 OR user_id IS NOT NULL) AND
        date_added >= '$day 00:00:00' AND date_added < '$day 24:00:00'
" );
echo "\nTotal Users: $total_users\n"; // Output total number of translators in the system so we can compare with previous number for example
?>

5. Current Total number and names of GTE per locale

<?php
/* The below script is just a guideline used in previous WPTranslationDays */
$gtes = $wpdb->get_results( "SELECT * FROM translate_translation_editors WHERE project_id != 0 GROUP BY user_id, locale, locale_slug" );
echo "\nGTEs: " . count($gtes) . "\n";

echo "User\tLocale\n";
foreach ($gte as $gte_user ) {
    echo get_user_by( 'id', $gte_user->user_id )->user_nicename; // Output GTE name
    echo "\t" . $gte_user->locale; // Output GTE locale
    echo "\n";
}
echo "\n";
?>

6. Current Total number of PTE per locale

<?php
/* The below script is just a guideline used in previous WPTranslationDays */
$ptes = $wpdb->get_results( "SELECT * FROM translate_translation_editors WHERE project_id != 0 GROUP BY user_id, locale, locale_slug" );
echo "\nNew PTEs: " . count($ptes) . "\n";

echo "User\tLocale\n";
foreach ($pte as $pte_user ) {
    echo get_user_by( 'id', $pte_user->user_id )->user_nicename; // Output PTE name
    echo "\t" . $pte_user->locale; // Output PTE locale
    echo "\n";
}
echo "\n";
?>

7. Latest 100 strings modified/added

We want to show some 'latest translated strings', so we would need the latest 100 (or 50 or fewer if 100 is really too much) translated strings with the following info:

• source string
• translated strings
• locale
• permalink (to the string, like ​https://translate.wordpress.org/projects/wp/dev/af/default?filters%5Bstatus%5D=either&filters%5Boriginal_id%5D=4835838&filters%5Btranslation_id%5D=50720799 for example)
• contributor (e.g. user_nicename of the translator)

Is it possible to add it into an API endpoint, please?

If there is some restrictions or incompatibilities, let us know :)
Best Regards

[mapk]

This was the initial concept I had regarding the design and functionality of the information.

• The boxes could be stacked liked this, or in a single line that is horizontally scrollable. The boxes in view would be the ones with the most activity at the time.
• We show the locales in the boxes with the number of strings translated below.
• On hover, the boxes show the strings that are being translated.
• The blinking box indicates that translations are happening there right now.

This is a rough idea, so I'm totally open to more thoughts.

[audrasjb]

Hello team, hello @ocean90 :)

@casiepa warned us that it would probably difficult to answer all our requests, so I propose to prioritize these requests.

If number 1 could be set up, it would be great.
I can give an example of API ideal rendering if you want :)

1) Total number of strings modified/added per locale since UTC 00:00
With this one only, and a regular refresh delay, we can provide a lot of live data.
I guess this is THE best one! :)

2) Latest 100 strings modified/added (source string, translated strings, locale, string permalink, contributor)
3) Total number of strings modified/added per project since UTC 00:00
4) Total number of current/waiting/rejected strings since UTC 00:00> Current Total translators in the system
5) Current Total number of GTE per locale and their usernames
6) Current Total number of PTE per locale and their usernames

Other question: can we get a list of all current available API endpoints concerning translations?
Is ​https://codex.wordpress.org/WordPress.org_API the full current list of endpoints?

Best Regards,
Jb

[dd32]

As the September translation day is over, is there anything in this ticket that's still needed?

[xkon]

@dd32 , No everything is done as it's a past event etc. Unfortunately the 'live' situation of the event forced us into using slack more rather than tickets at the end.

We can mark it as resolved ( I don't know what option to choose though so you might as well go ahead and do it if you like ). Thank you !

[tobifjellner]

That's easy. In the Action field, Click on Resolve and select reason (I used Worksforme).