[wp-meta] [Making WordPress.org] #3104: Getting the page ready for the live event

[Making WordPress.org]

#3104: Getting the page ready for the live event
-------------------------------------------------------+------------------
 Reporter:  xkon                                       |       Owner:
     Type:  enhancement                                |      Status:  new
 Priority:  normal                                     |   Milestone:
Component:  wptranslationday.org                       |  Resolution:
 Keywords:  ui-feedback ux-feedback needs-screenshots  |
-------------------------------------------------------+------------------

Comment (by 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
 <?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
 <?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
 <?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
 <?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
 <?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
 <?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

--
Ticket URL: <https://meta.trac.wordpress.org/ticket/3104#comment:4>
Making WordPress.org <https://meta.trac.wordpress.org/>
Making WordPress.org