<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head><meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>[12112] sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc: Translate: add a new WP-CLI command to get translation stats</title>
</head>
<body>
<style type="text/css"><!--
#msg dl.meta { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; }
#msg dl.meta dt { float: left; width: 6em; font-weight: bold; }
#msg dt:after { content:':';}
#msg dl, #msg dt, #msg ul, #msg li, #header, #footer, #logmsg { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; }
#msg dl a { font-weight: bold}
#msg dl a:link { color:#fc3; }
#msg dl a:active { color:#ff0; }
#msg dl a:visited { color:#cc6; }
h3 { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; font-weight: bold; }
#msg pre { white-space: pre-line; overflow: auto; background: #ffc; border: 1px #fa0 solid; padding: 6px; }
#logmsg { background: #ffc; border: 1px #fa0 solid; padding: 1em 1em 0 1em; }
#logmsg p, #logmsg pre, #logmsg blockquote { margin: 0 0 1em 0; }
#logmsg p, #logmsg li, #logmsg dt, #logmsg dd { line-height: 14pt; }
#logmsg h1, #logmsg h2, #logmsg h3, #logmsg h4, #logmsg h5, #logmsg h6 { margin: .5em 0; }
#logmsg h1:first-child, #logmsg h2:first-child, #logmsg h3:first-child, #logmsg h4:first-child, #logmsg h5:first-child, #logmsg h6:first-child { margin-top: 0; }
#logmsg ul, #logmsg ol { padding: 0; list-style-position: inside; margin: 0 0 0 1em; }
#logmsg ul { text-indent: -1em; padding-left: 1em; }#logmsg ol { text-indent: -1.5em; padding-left: 1.5em; }
#logmsg > ul, #logmsg > ol { margin: 0 0 1em 0; }
#logmsg pre { background: #eee; padding: 1em; }
#logmsg blockquote { border: 1px solid #fa0; border-left-width: 10px; padding: 1em 1em 0 1em; background: white;}
#logmsg dl { margin: 0; }
#logmsg dt { font-weight: bold; }
#logmsg dd { margin: 0; padding: 0 0 0.5em 0; }
#logmsg dd:before { content:'\00bb';}
#logmsg table { border-spacing: 0px; border-collapse: collapse; border-top: 4px solid #fa0; border-bottom: 1px solid #fa0; background: #fff; }
#logmsg table th { text-align: left; font-weight: normal; padding: 0.2em 0.5em; border-top: 1px dotted #fa0; }
#logmsg table td { text-align: right; border-top: 1px dotted #fa0; padding: 0.2em 0.5em; }
#logmsg table thead th { text-align: center; border-bottom: 1px solid #fa0; }
#logmsg table th.Corner { text-align: left; }
#logmsg hr { border: none 0; border-top: 2px dashed #fa0; height: 1px; }
#header, #footer { color: #fff; background: #636; border: 1px #300 solid; padding: 6px; }
#patch { width: 100%; }
#patch h4 {font-family: verdana,arial,helvetica,sans-serif;font-size:10pt;padding:8px;background:#369;color:#fff;margin:0;}
#patch .propset h4, #patch .binary h4 {margin:0;}
#patch pre {padding:0;line-height:1.2em;margin:0;}
#patch .diff {width:100%;background:#eee;padding: 0 0 10px 0;overflow:auto;}
#patch .propset .diff, #patch .binary .diff {padding:10px 0;}
#patch span {display:block;padding:0 10px;}
#patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;}
#patch ins {background:#dfd;text-decoration:none;display:block;padding:0 10px;}
#patch del {background:#fdd;text-decoration:none;display:block;padding:0 10px;}
#patch .lines, .info {color:#888;background:#fff;}
--></style>
<div id="msg">
<dl class="meta" style="font-size: 105%">
<dt style="float: left; width: 6em; font-weight: bold">Revision</dt> <dd><a style="font-weight: bold" href="http://meta.trac.wordpress.org/changeset/12112">12112</a><script type="application/ld+json">{"@context":"http://schema.org","@type":"EmailMessage","description":"Review this Commit","action":{"@type":"ViewAction","url":"http://meta.trac.wordpress.org/changeset/12112","name":"Review Commit"}}</script></dd>
<dt style="float: left; width: 6em; font-weight: bold">Author</dt> <dd>amieiro</dd>
<dt style="float: left; width: 6em; font-weight: bold">Date</dt> <dd>2022-10-10 12:22:48 +0000 (Mon, 10 Oct 2022)</dd>
</dl>
<pre style='padding-left: 1em; margin: 2em 0; border-left: 2px solid #ccc; line-height: 1.25; font-size: 105%; font-family: sans-serif'>Translate: add a new WP-CLI command to get translation stats</pre>
<h3>Modified Paths</h3>
<ul>
<li><a href="#sitestrunkwordpressorgpublic_htmlwpcontentpluginswporggpcustomizationsincclasspluginphp">sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/class-plugin.php</a></li>
</ul>
<h3>Added Paths</h3>
<ul>
<li><a href="#sitestrunkwordpressorgpublic_htmlwpcontentpluginswporggpcustomizationsinccliclassshowstatsphp">sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli/class-show-stats.php</a></li>
</ul>
</div>
<div id="patch">
<h3>Diff</h3>
<a id="sitestrunkwordpressorgpublic_htmlwpcontentpluginswporggpcustomizationsincclasspluginphp"></a>
<div class="modfile"><h4 style="background-color: #eee; color: inherit; margin: 1em 0; padding: 1.3em; font-size: 115%">Modified: sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/class-plugin.php</h4>
<pre class="diff"><span>
<span class="info" style="display: block; padding: 0 10px; color: #888">--- sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/class-plugin.php 2022-10-07 07:44:26 UTC (rev 12111)
+++ sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/class-plugin.php 2022-10-10 12:22:48 UTC (rev 12112)
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -5,6 +5,7 @@
</span><span class="cx" style="display: block; padding: 0 10px"> use GP;
</span><span class="cx" style="display: block; padding: 0 10px"> use GP_Locales;
</span><span class="cx" style="display: block; padding: 0 10px"> use GP_Translation;
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+use WordPressdotorg\GlotPress\Customizations\CLI\Show_Stats;
</ins><span class="cx" style="display: block; padding: 0 10px"> use WP_CLI;
</span><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> class Plugin {
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -447,6 +448,7 @@
</span><span class="cx" style="display: block; padding: 0 10px"> WP_CLI::add_command( 'wporg-translate make-core-pot', __NAMESPACE__ . '\CLI\Make_Core_Pot' );
</span><span class="cx" style="display: block; padding: 0 10px"> WP_CLI::add_command( 'wporg-translate export', __NAMESPACE__ . '\CLI\Export' );
</span><span class="cx" style="display: block; padding: 0 10px"> WP_CLI::add_command( 'wporg-translate export-json', __NAMESPACE__ . '\CLI\Export_Json' );
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+ WP_CLI::add_command( 'wporg-translate show-stats', __NAMESPACE__ . '\CLI\Show_Stats' );
</ins><span class="cx" style="display: block; padding: 0 10px">
</span><span class="cx" style="display: block; padding: 0 10px"> }
</span><span class="cx" style="display: block; padding: 0 10px">
</span></span></pre></div>
<a id="sitestrunkwordpressorgpublic_htmlwpcontentpluginswporggpcustomizationsinccliclassshowstatsphp"></a>
<div class="addfile"><h4 style="background-color: #eee; color: inherit; margin: 1em 0; padding: 1.3em; font-size: 115%">Added: sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli/class-show-stats.php</h4>
<pre class="diff"><span>
<span class="info" style="display: block; padding: 0 10px; color: #888">--- sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli/class-show-stats.php (rev 0)
+++ sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli/class-show-stats.php 2022-10-10 12:22:48 UTC (rev 12112)
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -0,0 +1,1232 @@
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+<?php
+/**
+ * This WP-CLI command has to be in the sandbox in the
+ * /home/wporg/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli folder
+ *
+ * To execute this command, you need to use this text in the CLI:
+ *
+ * wp wporg-translate show-stats --url=translate.wordpress.org
+ */
+
+namespace WordPressdotorg\GlotPress\Customizations\CLI;
+
+use DateTime;
+use Exception;
+use GP;
+use GP_Locale;
+use GP_Locales;
+use WP_CLI;
+use WP_CLI_Command;
+use WP_Query;
+use function WordPressdotorg\Locales\get_locales;
+
+class Show_Stats extends WP_CLI_Command {
+
+ /**
+ * Number of years backward from which you want to obtain statistics.
+ * First translation was added to the database at 2010-02-17 18:09:13.
+ *
+ * @var int
+ */
+ private int $number_of_years = 15;
+
+ /**
+ * Number of translators with more translations each year to get feedback from.
+ *
+ * @var int
+ */
+ private int $number_of_translators = 100;
+
+ /**
+ * First user at DotOrg registered the current year.
+ *
+ * @var int
+ */
+ private int $id_first_user_of_this_year = 0;
+
+ /**
+ * Id of each forum database, for each country
+ *
+ * @var int[]
+ */
+ private array $forum_ids = array(
+ 'pt' => 383,
+ 'make' => 384,
+// '' => 385,
+ 'emoji' => 386,
+ 'hau' => 387,
+ 'fao' => 388,
+ 'af' => 389,
+ 'am' => 390,
+ 'id' => 391,
+ 'mya' => 392,
+ 'ja' => 393,
+ 'ru' => 394,
+ 'de' => 395,
+ 'bs' => 396,
+ 'th' => 397,
+ 'cn' => 398,
+ 'ak' => 399,
+ 'an' => 400,
+ 'ar' => 401,
+ 'arq' => 402,
+ 'ug' => 403,
+ 'tw' => 404,
+ 'bg' => 405,
+ 'sr' => 406,
+ 'hr' => 407,
+ 'nl' => 408,
+ 'he' => 409,
+ 'ka' => 410,
+ 'fi' => 411,
+ 'mk' => 412,
+ 'ca' => 413,
+ 'sk' => 414,
+ 'pl' => 415,
+ 'ro' => 416,
+ 'es' => 417,
+ 'br' => 418,
+ 'en' => 419,
+ 'sq' => 420,
+ 'hy' => 421,
+ 'rup' => 422,
+ 'frp' => 423,
+ 'as' => 424,
+ 'ast' => 425,
+ 'az' => 426,
+ 'az-tr' => 427,
+ 'bcc' => 428,
+ 'eu' => 429,
+ 'bel' => 430,
+ 'bn' => 431,
+ 'bre' => 432,
+ 'ceb' => 433,
+ 'zh-hk' => 434,
+ 'co' => 435,
+ 'cs' => 436,
+ 'et' => 437,
+ 'eo' => 438,
+ 'uz' => 439,
+ );
+
+ private string $header = '';
+ private string $originals_by_year = '';
+ private string $translations_translators_by_year = '';
+ private string $forum_post_and_replies_by_year = '';
+ private string $wordpress_translation_percentage = '';
+ private string $packages_generated_by_year = '';
+ private string $themes_plugins_by_year = '';
+ private string $feedback_received = '';
+ private string $contributors_per_locale = '';
+ private string $managers_stats = '';
+ private string $most_active_translators = '';
+
+ /**
+ * The name of the custom post type used to the translation feedback.
+ */
+ private const FEEDBACK_POST_TYPE = 'gth_original';
+
+ /**
+ * Shows Polyglots stats.
+ */
+ public function __invoke( $args, $assoc_args ) {
+ $this->set_number_of_years_with_data();
+ $this->print_header();
+ $this->print_wordpress_translation_percentage();
+ $this->print_packages_generated();
+ $this->print_unique_themes_plugins_by_year();
+ $this->print_originals_natural_year();
+ $this->print_total_translations_translators_by_year();
+ $this->print_forum_by_locale_and_year();
+ $this->print_feedback_received();
+ $this->print_contributors_per_locale();
+ $this->print_managers_stats();
+ $this->print_most_active_translators();
+ $this->update_page();
+ }
+
+ /**
+ * Set the number of years between 2010 (first year with translations) and the current year.
+ *
+ * @return void
+ */
+ private function set_number_of_years_with_data(): void {
+ $this->number_of_years = gmdate( 'Y' ) - 2010 + 1;
+ }
+
+ /**
+ * Print the main header.
+ *
+ * @return void
+ */
+ private function print_header(): void {
+ $this->header = $this->create_gutenberg_paragraph( 'Polyglots stats. Created at ' . gmdate( 'Y-m-d H:i:s' ) . ' ' . date_default_timezone_get() );
+ $this->header .= $this->create_gutenberg_paragraph( 'Created using the <b>wp wporg-translate show-stats --url=translate.wordpress.org</b> command.' );
+ $this->print_wpcli_heading( 'Polyglots stats. Created at ' . gmdate( 'Y-m-d H:i:s' ) . ' ' . date_default_timezone_get() );
+ }
+
+ /**
+ * Print the number of original strings grouped by year.
+ *
+ * @return void
+ */
+ private function print_originals_natural_year(): void {
+ global $wpdb;
+ $originals = $wpdb->get_results(
+ "SELECT
+ YEAR( date_added ) as year,
+ count(*) as strings
+ FROM {$wpdb->gp_originals}
+ GROUP BY YEAR( date_added )
+ ORDER BY YEAR( date_added ) DESC"
+ );
+
+ $this->originals_by_year = $this->create_gutenberg_heading( 'Number of originals in the last ' . $this->number_of_years . ' years.' );
+ $this->print_wpcli_heading( 'Number of originals in the last ' . $this->number_of_years . ' years.' );
+
+ $code = "Year \t\t Number of strings" . PHP_EOL;
+ $code .= '................................................................' . PHP_EOL;
+
+ foreach ( $originals as $original ) {
+ if ( gmdate( 'Y' ) == $original->year ) {
+ $originals_estimated = $this->estimate_value_for_full_year( $original->strings );
+ $code .= $original->year . " (*) \t " . number_format_i18n( $originals_estimated ) . PHP_EOL;
+ }
+ $code .= $original->year . " \t\t " . number_format_i18n( $original->strings ) . PHP_EOL;
+ }
+ $code .= '................................................................' . PHP_EOL;
+ $code .= '(*) Estimated for the current year.' . PHP_EOL;
+ $code .= PHP_EOL;
+
+ $this->originals_by_year .= $this->create_gutenberg_code( $code );
+ WP_CLI::log( $code );
+ }
+
+ /**
+ * Print the number of locales, with and without variants, and the % of core translation in each locale.
+ *
+ * @return void
+ */
+ private function print_wordpress_translation_percentage(): void {
+ global $wpdb;
+ $core_total = $this->get_core_total();
+ $core_100 = $this->get_core_full_translated();
+ $core_95_100 = $this->get_core_interval( 100, 95 );
+ $core_90_95 = $this->get_core_interval( 95, 90 );
+ $core_50_90 = $this->get_core_interval( 90, 50 );
+ $core_50 = $this->get_core_interval( 50, 0, '<', '>' );
+ $core_0 = $this->get_core_empty_translated();
+
+ $this->wordpress_translation_percentage = $this->create_gutenberg_heading( 'WordPress core: Translated percentage by the locale.' );
+ $this->print_wpcli_heading( 'WordPress core: Translated percentage by the locale.' );
+ $code = '................................................................' . PHP_EOL;
+ $code .= "Number of locales: \t" . number_format_i18n( count( $this->get_existing_locales() ) ) . PHP_EOL;
+ $code .= "Number of locales with variants: \t" . number_format_i18n( count( $this->get_existing_locales( 'with_variants' ) ) ) . PHP_EOL;
+ $code .= '................................................................' . PHP_EOL;
+ $code .= 'Info from the historical stats.' . PHP_EOL;
+ $code .= "Number of WordPress (wp/dev) to translate: \t" . $core_total . PHP_EOL;
+ $code .= "100% WordPress translated: \t" . $core_100 . PHP_EOL;
+ $code .= "95-100% WordPress translated: \t" . $core_95_100 . PHP_EOL;
+ $code .= "90-95% WordPress translated: \t" . $core_90_95 . PHP_EOL;
+ $code .= "50-90% WordPress translated: \t" . $core_50_90 . PHP_EOL;
+ $code .= "0-50% WordPress translated: \t" . $core_50 . PHP_EOL;
+ $code .= "0% WordPress translated: \t" . $core_0 . PHP_EOL;
+ $code .= '................................................................' . PHP_EOL;
+ $code .= 'The difference between the number of locales and the number of ' . PHP_EOL;
+ $code .= "WordPress (wp/dev) is due to some duplicated variants. \n" . PHP_EOL;
+
+ $this->wordpress_translation_percentage .= $this->create_gutenberg_code( $code );
+ WP_CLI::log( $code );
+ }
+
+ /**
+ * Print the packages generated each year.
+ *
+ * @return void
+ */
+ private function print_packages_generated() {
+ global $wpdb;
+
+ $packages = $wpdb->get_results(
+ "SELECT
+ LEFT( updated, 4 ) as year,
+ SUM( CASE WHEN type = 'core' THEN 1 ELSE 0 END ) as core_packs,
+ SUM( CASE WHEN type = 'plugin' THEN 1 ELSE 0 END ) as plugin_packs,
+ SUM( CASE WHEN type = 'theme' THEN 1 ELSE 0 END ) as theme_packs,
+ count(*) as total_packs
+ FROM language_packs
+ WHERE updated >= '2010-01-01'
+ GROUP BY LEFT( updated, 4 )
+ ORDER BY LEFT( updated, 4 ) DESC",
+ ARRAY_A );
+
+ $header = 'Language Packs generated per year.';
+ $this->packages_generated_by_year = $this->create_gutenberg_heading( $header );
+ $this->print_wpcli_heading( $header );
+ $code = "Year \t Core Packs \t Plugin Packs \t Theme Packs \t Total Packs " . PHP_EOL;
+ $code .= '.......................................................................' . PHP_EOL;
+
+ foreach ( $packages as $package ) {
+ if ( gmdate( 'Y' ) == $package['year'] ) {
+ $code .= str_pad( $package['year'] . ' (*) ', 10 ) .
+ str_pad( number_format_i18n( $this->estimate_value_for_full_year( $package['core_packs']) ), 16 ) .
+ str_pad( number_format_i18n( $this->estimate_value_for_full_year( $package['plugin_packs'] ) ), 16 ) .
+ str_pad( number_format_i18n( $this->estimate_value_for_full_year( $package['theme_packs'] ) ), 16 ) .
+ str_pad( number_format_i18n( $this->estimate_value_for_full_year( $package['total_packs'] ) ), 16 ) .
+ PHP_EOL;
+ }
+ $code .= str_pad( $package['year'], 10 ) .
+ str_pad( number_format_i18n( $package['core_packs'] ), 16 ) .
+ str_pad( number_format_i18n( $package['plugin_packs'] ), 16 ) .
+ str_pad( number_format_i18n( $package['theme_packs'] ), 16 ) .
+ str_pad( number_format_i18n( $package['total_packs'] ), 16 ) .
+ PHP_EOL;
+ }
+ $code .= '.......................................................................' . PHP_EOL;
+ $code .= '(*) Estimated for the current year.' . PHP_EOL;
+
+ $this->packages_generated_by_year .= $this->create_gutenberg_code( $code );
+ WP_CLI::log( $code );
+ }
+
+ /**
+ * Print the Unique Plugins / Themes language packs per year.
+ *
+ * @return void
+ */
+ private function print_unique_themes_plugins_by_year() {
+ global $wpdb;
+
+ $packages = $wpdb->get_results(
+ "SELECT
+ year,
+ SUM( CASE WHEN type = 'plugin' THEN 1 ELSE 0 END ) as plugins,
+ SUM( CASE WHEN type = 'theme' THEN 1 ELSE 0 END ) as themes,
+ count(*) as total
+ FROM (
+ SELECT
+ domain,
+ type,
+ LEFT( updated, 4 ) as year
+ FROM language_packs
+ WHERE updated >= '2010-01-01' AND type IN( 'plugin', 'theme' )
+ GROUP BY domain, type, LEFT( updated, 4 )
+ ORDER BY type, LEFT( updated, 4 )+0 ASC
+ )a
+ GROUP BY year
+ ORDER BY year DESC",
+ ARRAY_A );
+
+ $header = 'Unique Plugins / Themes language packs per year.';
+ $this->themes_plugins_by_year = $this->create_gutenberg_heading( $header );
+ $this->print_wpcli_heading( $header );
+ $code = "Year \t Plugins \t Themes \t Total" . PHP_EOL;
+ $code .= '................................................................' . PHP_EOL;
+
+ foreach ( $packages as $package ) {
+ if ( gmdate( 'Y' ) == $package['year'] ) {
+ $code .= str_pad( $package['year'] . ' (*) ', 10 ) .
+ str_pad( number_format_i18n( $this->estimate_value_for_full_year( $package['plugins']) ), 16 ) .
+ str_pad( number_format_i18n( $this->estimate_value_for_full_year( $package['themes'] ) ), 16 ) .
+ str_pad( number_format_i18n( $this->estimate_value_for_full_year( $package['total'] ) ), 16 ) .
+ PHP_EOL;
+ }
+ $code .= str_pad( $package['year'], 10 ) .
+ str_pad( number_format_i18n( $package['plugins'] ), 16 ) .
+ str_pad( number_format_i18n( $package['themes'] ), 16 ) .
+ str_pad( number_format_i18n( $package['total'] ), 16 ) .
+ PHP_EOL;
+ }
+ $code .= '................................................................' . PHP_EOL;
+ $code .= '(*) Estimated for the current year.' . PHP_EOL;
+
+ $this->themes_plugins_by_year .= $this->create_gutenberg_code( $code );
+ WP_CLI::log( $code );
+ }
+
+ /**
+ * Print the number of translations and translators in the last years.
+ *
+ * We don't use a query like this one because we get MySQL timeout:
+ * $translators = $wpdb->get_var(
+ * $wpdb->prepare(
+ * "SELECT COUNT( DISTINCT user_id) as translators
+ * FROM translate_translations
+ * WHERE date_modified >= '%s' and date_modified <= '%s'",
+ * '2022-01-01 00:00:00',
+ * '2022-12-31 23:59:59'
+ * ) );
+ *
+ * We get the first and the last translation id for each year and
+ * then count the number of different translators.
+ *
+ * @return void
+ */
+ private function print_total_translations_translators_by_year() {
+ global $wpdb;
+ $this->translations_translators_by_year = $this->create_gutenberg_heading( 'Number of translations and translators in the last ' . $this->number_of_years . ' years.' );
+ $this->print_wpcli_heading( 'Number of translations and translators in the last ' . $this->number_of_years . ' years.' );
+ $code = "Year \t\t Number of translations \t Translators \t Translators with > 1 translation" . PHP_EOL;
+ $code .= '................................................................' . PHP_EOL;
+ $last_year = gmdate( 'Y' );
+ $first_year = $last_year - $this->number_of_years;
+ $first_id = 0;
+ for ( $year = $last_year; $year > $first_year; $year -- ) {
+ if ( gmdate( 'Y' ) == $year ) {
+ $last_id = $wpdb->get_var( "SELECT MAX(id) FROM {$wpdb->gp_translations}" );
+ } else {
+ $last_id = $first_id - 1;
+ }
+ $starting_date = $year . '-01-01 00:00:00';
+ $first_id = $wpdb->get_var( $wpdb->prepare(
+ "SELECT MIN(id) FROM {$wpdb->gp_translations} WHERE date_added >= %s",
+ $starting_date
+ ) );
+
+ $row = $wpdb->get_row( $wpdb->prepare(
+ "SELECT
+ COUNT(*) as strings_added,
+ COUNT( DISTINCT user_id ) as contributors
+ FROM {$wpdb->gp_translations}
+ WHERE id BETWEEN %d AND %d",
+ $first_id,
+ $last_id,
+ ), ARRAY_A ); // phpcs:ignore PEAR.Functions.FunctionCallSignature.MultipleArguments
+
+ $strings_added = number_format_i18n( $row['strings_added'] );
+ $contributors = number_format_i18n( $row['contributors'] );
+
+ $wpdb->get_results( $wpdb->prepare(
+ "SELECT
+ DISTINCT user_id, count(id) as translation_number
+ FROM {$wpdb->gp_translations}
+ WHERE id BETWEEN %d AND %d
+ GROUP BY user_id
+ HAVING( COUNT(id) > 1 )",
+ $first_id,
+ $last_id,
+ ), ARRAY_A ); // phpcs:ignore PEAR.Functions.FunctionCallSignature.MultipleArguments
+ $repeat_contributors_rows = $wpdb->num_rows;
+
+ if ( gmdate( 'Y' ) == $year ) {
+ $strings_added = str_pad( number_format_i18n( $this->estimate_value_for_full_year( $row['strings_added'] ) ), 10, ' ', STR_PAD_LEFT );
+ $contributors = str_pad( number_format_i18n( $this->estimate_value_for_full_year( $row['contributors'] ) ), 6, ' ', STR_PAD_LEFT );
+ $repeat_contributors = str_pad( number_format_i18n( $this->estimate_value_for_full_year( $repeat_contributors_rows ) ), 8, ' ', STR_PAD_LEFT );
+ $code .= "{$year} (*) \t {$strings_added} \t\t\t {$contributors} \t {$repeat_contributors}" . PHP_EOL;
+ }
+ $strings_added = str_pad( number_format_i18n( $row['strings_added'] ), 10, ' ', STR_PAD_LEFT );
+ $contributors = str_pad( number_format_i18n( $row['contributors'] ), 6, ' ', STR_PAD_LEFT );
+ $repeat_contributors = str_pad( number_format_i18n( $repeat_contributors_rows ), 8, ' ', STR_PAD_LEFT );
+ $code .= "{$year} \t\t {$strings_added} \t\t\t {$contributors} \t {$repeat_contributors}" . PHP_EOL;
+ }
+ $code .= '................................................................' . PHP_EOL;
+ $code .= '(*) Estimated for the current year.' . PHP_EOL;
+ $code .= PHP_EOL;
+
+ $this->translations_translators_by_year .= $this->create_gutenberg_code( $code );
+ WP_CLI::log( $code );
+ }
+
+ private function print_forum_by_locale_and_year() {
+ $last_year = gmdate( 'Y' );
+ $first_year = $last_year - $this->number_of_years;
+ $forum_posts = array();
+ $forum_replies = array();
+ $code = '';
+ for ( $year = $last_year; $year > $first_year; $year -- ) {
+ $forum_posts[ $year ] = $this->get_forums_stats( 'topic', $year );
+ $forum_replies[ $year ] = $this->get_forums_stats( 'reply', $year );
+ }
+ ksort($this->forum_ids);
+ $header = 'Forums. Topics by year and locale.';
+ $this->forum_post_and_replies_by_year = $this->create_gutenberg_heading( $header );
+ $this->print_wpcli_heading( $header );
+
+ $code .= str_pad('Locale', 12 );
+ for ( $year = $last_year; $year > $first_year; $year -- ) {
+ $code .= str_pad( $year, 8,' ', STR_PAD_LEFT );
+ }
+ $code .= PHP_EOL;
+ foreach ( $this->forum_ids as $key => $value ) {
+ $code .= str_pad($key, 12 );
+ for ( $year = $last_year; $year > $first_year; $year -- ) {
+ $code .= str_pad( number_format_i18n( $forum_posts[$year][$key] ), 8,' ', STR_PAD_LEFT );
+ }
+ $code .= PHP_EOL;
+ }
+ $this->forum_post_and_replies_by_year .= $this->create_gutenberg_code( $code );
+ WP_CLI::log( $code );
+
+ $header = 'Forums. Replies by year and locale.';
+ $this->forum_post_and_replies_by_year .= $this->create_gutenberg_heading( $header );
+ $this->print_wpcli_heading( $header );
+
+ $code = str_pad('Locale', 12 );
+ for ( $year = $last_year; $year > $first_year; $year -- ) {
+ $code .= str_pad( $year, 8,' ', STR_PAD_LEFT );
+ }
+ $code .= PHP_EOL;
+ foreach ( $this->forum_ids as $key => $value ) {
+ $code .= str_pad($key, 12 );
+ for ( $year = $last_year; $year > $first_year; $year -- ) {
+ $code .= str_pad( number_format_i18n( $forum_replies[$year][$key] ), 8,' ', STR_PAD_LEFT );
+ }
+ $code .= PHP_EOL;
+ }
+ $this->forum_post_and_replies_by_year .= $this->create_gutenberg_code( $code );
+ WP_CLI::log( $code );
+ }
+
+ /**
+ * Print the most active translators in the last years.
+ *
+ * @return void
+ */
+ private function print_most_active_translators(): void {
+ global $wpdb;
+
+ $last_year = gmdate( 'Y' );
+ $first_year = $last_year - $this->number_of_years;
+ $first_id = 0;
+ $this->most_active_translators = $this->create_gutenberg_heading( 'Most active translators in the last ' . $this->number_of_years . ' years.' );
+ $this->print_wpcli_heading( 'Most active translators in the last ' . $this->number_of_years . ' years.' );
+ $code = "Year \t Translations \t Translator" . PHP_EOL;
+ for ( $year = $last_year; $year > $first_year; $year -- ) {
+ if ( gmdate( 'Y' ) == $year ) {
+ $last_id = $wpdb->get_var( "SELECT MAX(id) FROM {$wpdb->gp_translations}" );
+ } else {
+ $last_id = $first_id - 1;
+ }
+ $starting_date = $year . '-01-01 00:00:00';
+ $first_id = $wpdb->get_var( $wpdb->prepare(
+ "SELECT MIN(id) FROM {$wpdb->gp_translations} WHERE date_added >= %s",
+ $starting_date
+ ) );
+
+ $rows = $wpdb->get_results( $wpdb->prepare(
+ "SELECT
+ user_id,
+ COUNT(*) as strings_added
+ FROM {$wpdb->gp_translations}
+ WHERE id BETWEEN %d AND %d
+ GROUP BY user_id
+ ORDER BY `strings_added` DESC
+ LIMIT %d",
+ $first_id,
+ $last_id,
+ $this->number_of_translators
+ ), ARRAY_A ); // phpcs:ignore PEAR.Functions.FunctionCallSignature.MultipleArguments
+ $code .= '................................................................' . PHP_EOL;
+ foreach ( $rows as $row ) {
+ if ( 0 == $row['user_id'] ) {
+ continue;
+ }
+ $strings_added = number_format_i18n( $row['strings_added'] );
+ $contributor = get_user_by( 'id', $row['user_id'] );
+ $code .= "{$year} \t {$strings_added} \t {$contributor->user_login}" . PHP_EOL;
+ }
+ }
+ $this->most_active_translators .= $this->create_gutenberg_code( $code );
+ WP_CLI::log( $code );
+ }
+
+ /**
+ * Estimate the value for the end of the year, using a direct rule of 3.
+ *
+ * @param int $current_value The value used to calculate the estimation at the end of the year.
+ *
+ * @return int The estimated value.
+ */
+ private function estimate_value_for_full_year( int $current_value ): int {
+ $current_day_of_year = gmdate( 'z' ) + 1;
+ $days_in_current_year = date_diff( new DateTime( 'last day of december' ), new DateTime( 'first day of january' ) )->days + 1;
+
+ return round( $current_value * $days_in_current_year / $current_day_of_year );
+ }
+
+ /**
+ *
+ * We release the feedback functionality on July 28, 2022
+ * in the Polyglots Coffee Break
+ * https://make.wordpress.org/polyglots/2022/06/28/polyglots-coffee-break-july-28-2022-at-2200-utc/
+ *
+ * @return void
+ */
+ private function print_feedback_received() {
+ global $wpdb;
+ $original_strings_with_comments = 0;
+ $total_comments = 0;
+ $optin_users = 0;
+ $status_counter = array();
+ $comment_meta_translation_ids = array();
+ $comment_user_ids = array();
+ $commenters_number = 0;
+ $commenters = array();
+
+ // Get the number of opt-in users.
+ $optin_users = number_format_i18n( $wpdb->get_var( $wpdb->prepare(
+ "SELECT COUNT(user_id) as optin_users FROM {$wpdb->usermeta}
+ WHERE meta_key = %s AND meta_value LIKE %s",
+ 'wporg_351_gp_default_sort',
+ '%s:19:\"notifications_optin\";s:2:\"on\";%' ) ) );
+
+ // Get the number of original strings with comments: one CPT for each original.
+ $feedback_posts_args = array(
+ 'posts_per_page' => - 1,
+ 'post_status' => 'publish',
+ 'post_type' => $this::FEEDBACK_POST_TYPE,
+ 'date_query' => array(
+ array( 'after' => '2022-07-28' ),
+ ),
+ );
+ $feedback_posts = new WP_Query( $feedback_posts_args );
+ $original_strings_with_comments = number_format_i18n( $feedback_posts->post_count );
+
+ // Get the total number of comments.
+ $feedback_comments_args = array(
+ 'number' => - 1,
+ 'post_type' => $this::FEEDBACK_POST_TYPE,
+ 'count' => true,
+ 'date_query' => array(
+ array( 'after' => '2022-07-28' ),
+ ),
+ );
+ $total_comments = number_format_i18n( get_comments( $feedback_comments_args ) );
+
+ // Get some info related with the status of the translations who get feedback.
+ // First, get the comments related with a translation, because we can get comments related
+ // only with the original.
+ $feedback_comments_args = array(
+ 'post_type' => $this::FEEDBACK_POST_TYPE,
+ 'count' => false,
+ 'meta_key' => 'translation_id',
+ 'date_query' => array(
+ array( 'after' => '2022-07-28' ),
+ ),
+ );
+ $comments = get_comments( $feedback_comments_args );
+
+ // Get the translation ids with a feedback comment.
+ foreach ( $comments as $comment ) {
+ $comment_meta_translation_ids[] = get_comment_meta( $comment->comment_post_ID, 'translation_id', true );
+ }
+ $comment_meta_translation_ids = array_unique( $comment_meta_translation_ids );
+
+ // Check all comments with a related translation.
+ foreach ( $comment_meta_translation_ids as $comment_meta_translation_id ) {
+ $translation = $wpdb->get_row(
+ $wpdb->prepare(
+ 'SELECT translate_translation_sets.locale, translate_translations.id, translate_translations.original_id,
+ translate_translations.user_id, translate_translations.status
+ FROM translate_translations
+ INNER JOIN translate_translation_sets on translate_translations.translation_set_id=translate_translation_sets.id
+ WHERE translate_translations.id= %s',
+ $comment_meta_translation_id
+ )
+ );
+
+ // If this translation was rejected, I look for a current translation for the same original (original_id),
+ // translator (user_id) and language (locale).
+ if ( 'rejected' == $translation->status ) {
+ $is_current_from_rejection = $wpdb->get_row(
+ $wpdb->prepare(
+ "SELECT translate_translations.id, translate_translation_sets.locale
+ FROM translate_translations
+ INNER JOIN translate_translation_sets on translate_translations.translation_set_id=translate_translation_sets.id
+ WHERE
+ translate_translations.status='current'
+ AND translate_translations.original_id=%d
+ AND translate_translations.user_id=%d
+ AND translate_translation_sets.locale=%s",
+ $translation->original_id,
+ $translation->user_id,
+ $translation->locale
+ )
+ );
+ if ( $is_current_from_rejection ) {
+ $status_counter['current_from_rejection'] ++;
+ }
+ }
+ $status_counter[ $translation->status ] ++;
+ }
+
+ // Get most active commenter's.
+ $most_active_commenters_args = array(
+ 'post_type' => $this::FEEDBACK_POST_TYPE,
+ 'count' => false,
+ 'date_query' => array(
+ array( 'after' => '2022-07-28' ),
+ ),
+ );
+ $comments = get_comments( $most_active_commenters_args );
+ foreach ( $comments as $comment ) {
+ $comment_user_ids[] = $comment->user_id;
+ }
+ $commenters_with_comment_count = array_count_values( $comment_user_ids );
+ arsort( $commenters_with_comment_count );
+ $commenters_number = number_format_i18n( count( $commenters_with_comment_count ) );
+ foreach ( $commenters_with_comment_count as $user_id => $comment_number ) {
+ $user = get_user_by( 'id', $user_id );
+ $user->comments_number = $comment_number;
+ $commenters[] = $user;
+ }
+ // Format and print the info.
+ $comment_meta_translation_id_number = number_format_i18n( count( $comment_meta_translation_ids ) );
+ $rejected_number = number_format_i18n( $status_counter['rejected'] );
+ $current_from_rejection_number = number_format_i18n( $status_counter['current_from_rejection'] );
+ $current_number = number_format_i18n( $status_counter['current'] );
+ $fuzzy_number = number_format_i18n( $status_counter['fuzzy'] );
+ $old_number = number_format_i18n( $status_counter['old'] );
+ $this->feedback_received = $this->create_gutenberg_heading( 'Feedback in the last ' . $this->number_of_years . ' years (starting on 2022-07-28).' );
+ $this->print_wpcli_heading( 'Feedback in the last ' . $this->number_of_years . ' years (starting on 2022-07-28).' );
+ $code = "Opt-in users: \t\t\t\t\t {$optin_users}" . PHP_EOL;
+ $code .= "Original strings with comments: \t\t {$original_strings_with_comments}" . PHP_EOL;
+ $code .= "Comments: \t\t\t\t\t {$total_comments}" . PHP_EOL;
+ $code .= "Different translations with comments: \t\t {$comment_meta_translation_id_number}" . PHP_EOL;
+ $code .= " - Rejected translations: \t\t\t {$rejected_number}" . PHP_EOL;
+ $code .= " - Approved translations from a rejection: \t {$current_from_rejection_number}" . PHP_EOL;
+ $code .= " - Approved translations (not rejected): \t {$current_number}" . PHP_EOL;
+ $code .= " - Fuzzy translations (not rejected): \t\t {$fuzzy_number}" . PHP_EOL;
+ $code .= " - Old translations: \t\t\t\t {$old_number}" . PHP_EOL;
+ $code .= "Number of different commenters: \t\t {$commenters_number}" . PHP_EOL;
+ foreach ( $commenters as $commenter ) {
+ if ( strlen( $commenter->user_login ) > 10 ) {
+ $tabs = "\t";
+ } else {
+ $tabs = "\t\t";
+ }
+ $url = 'https://profiles.wordpress.org/' . sanitize_title_with_dashes( $commenter->user_login );
+ $comments_number = number_format_i18n( $commenter->comments_number );
+ $code .= " - {$commenter->user_login}: {$tabs} {$comments_number} comments. Profile: {$url}" . PHP_EOL;
+ }
+ $code .= PHP_EOL;
+ $this->feedback_received .= $this->create_gutenberg_code( $code );
+ WP_CLI::log( $code );
+ }
+
+ /**
+ * Print the total number of LM, GTE and PTE and grouped by locale.
+ *
+ * @return void
+ */
+ private function print_managers_stats() {
+ $this->id_first_user_of_this_year = $this->get_id_first_user_of_this_year();
+ $locales = get_locales();
+
+ $locale_managers = $this->count_managers( 'locale_manager', 'total' );
+ $registered_this_year_new_locale_managers = $this->count_managers( 'locale_manager', 'registered_this_year' );
+ $started_this_year_new_locale_managers = $this->count_managers( 'locale_manager', 'started_this_year' );
+
+ $general_translation_editors = $this->count_managers( 'general_translation_editor' );
+ $registered_this_year_new_general_translation_editors = $this->count_managers( 'general_translation_editor', 'registered_this_year' );
+ $started_this_year_new_general_translation_editors = $this->count_managers( 'general_translation_editor', 'started_this_year' );
+
+ $project_translation_editors = $this->count_managers( 'translation_editor' );
+ $registered_this_year_new_project_translation_editors = $this->count_managers( 'translation_editor', 'registered_this_year' );
+ $started_this_year_new_translation_editor = $this->count_managers( 'translation_editor', 'started_this_year' );
+
+ $this->managers_stats = $this->create_gutenberg_heading( 'Managers stats.' );
+ $this->print_wpcli_heading( 'Managers stats.' );
+ $code = 'Local managers (LM):' . PHP_EOL;
+ $code .= " - Total:\t\t\t\t\t\t\t\t" . number_format_i18n( array_sum( $locale_managers ) ) . PHP_EOL;
+ $code .= " - Total users that have been registered this year and get the role:\t" . number_format_i18n( array_sum( $registered_this_year_new_locale_managers ) ) . PHP_EOL;
+ $code .= " - Total users that have start translating this year and get the role:\t" . number_format_i18n( array_sum( $started_this_year_new_locale_managers ) ) . PHP_EOL;
+ foreach ( $locales as $locale ) {
+ if ( array_key_exists( $locale->english_name, $locale_managers ) ) {
+ $code .= "\t - " . $locale->english_name . ': ' . number_format_i18n( $locale_managers[ $locale->english_name ] ) . ', ' . number_format_i18n( $registered_this_year_new_locale_managers[ $locale->english_name ] ) . ', ' . number_format_i18n( $started_this_year_new_locale_managers[ $locale->english_name ] ) . PHP_EOL;
+ }
+ }
+ $code .= 'General Translator Editors (GTE):' . PHP_EOL;
+ $code .= " - Total:\t\t\t\t\t\t\t\t" . number_format_i18n( array_sum( $general_translation_editors ) ). PHP_EOL;
+ $code .= " - Total users that have been registered this year and get the role:\t" . number_format_i18n( array_sum( $registered_this_year_new_general_translation_editors ) ) . PHP_EOL;
+ $code .= " - Total users that have start translating this year and get the role:\t" . number_format_i18n( array_sum( $started_this_year_new_general_translation_editors ) ) . PHP_EOL;
+ foreach ( $locales as $locale ) {
+ if ( array_key_exists( $locale->english_name, $locale_managers ) ) {
+ $code .= "\t - " . $locale->english_name . ': ' . number_format_i18n( $general_translation_editors[ $locale->english_name ] ) . ', ' . number_format_i18n( $registered_this_year_new_general_translation_editors[ $locale->english_name ] ) . ', ' . number_format_i18n( $started_this_year_new_general_translation_editors[ $locale->english_name ] ) . PHP_EOL;
+ }
+ }
+ $code .= 'Project Translation Editors (PTE):' . PHP_EOL;
+ $code .= " - Total:\t\t\t\t\t\t\t\t" . number_format_i18n( array_sum( $project_translation_editors ) ) . PHP_EOL;
+ $code .= " - Total users that have been registered this year and get the role:\t" . number_format_i18n( array_sum( $registered_this_year_new_project_translation_editors ) ) . PHP_EOL;
+ $code .= " - Total users that have start translating this year and get the role:\t" . number_format_i18n( array_sum( $started_this_year_new_translation_editor ) ) . PHP_EOL;
+ foreach ( $locales as $locale ) {
+ if ( array_key_exists( $locale->english_name, $locale_managers ) ) {
+ $code .= "\t - " . $locale->english_name . ': ' . number_format_i18n( $project_translation_editors[ $locale->english_name ] ) . ', ' . number_format_i18n( $registered_this_year_new_project_translation_editors[ $locale->english_name ] ) . ', ' . number_format_i18n( $started_this_year_new_translation_editor[ $locale->english_name ] ) . PHP_EOL;
+ }
+ }
+ $this->managers_stats .= $this->create_gutenberg_code( $code );
+
+ WP_CLI::log( $code );
+ }
+
+ /**
+ * Print the number of contributors per locale.
+ *
+ * @return void
+ */
+ private function print_contributors_per_locale():void {
+ $locales = get_locales();
+ $header = 'Contributors per locale';
+ $this->contributors_per_locale = $this->create_gutenberg_heading( $header );
+ $this->print_wpcli_heading( $header );
+ $code = '.........................................................................................' . PHP_EOL;
+ $code .= 'Active contributor: 1 translation in the last 365 days.' . PHP_EOL;
+ $code .= '.........................................................................................' . PHP_EOL;
+ $code .= "Locale \t\t\t\t Active contributors Past contributors All contributors" . PHP_EOL;
+ $code .= '.........................................................................................' . PHP_EOL;
+ foreach ( $locales as $locale ) {
+ $current_contributors = $this->get_translation_contributors( $locale, 365 );
+ $all_contributors = $this->get_translation_contributors( $locale );
+ $code .= str_pad( $locale->english_name, 40) .
+ str_pad(number_format_i18n( count( $current_contributors ) ), 20) .
+ str_pad(number_format_i18n( count($all_contributors) - count( $current_contributors ) ), 20) .
+ str_pad(number_format_i18n( count( $all_contributors ) ), 20) . PHP_EOL;
+ }
+ $this->contributors_per_locale .= $this->create_gutenberg_code( $code );
+
+ WP_CLI::log( $code );
+ }
+
+ /**
+ * Get the locales at DotOrg.
+ *
+ * @param string $locale_slug Locale slug.
+ * - 'default' for the default locales without variants.
+ * - 'with_variants' for the default locales with variants.
+ *
+ * @return array Number of locales
+ */
+ private function get_existing_locales( string $locale_slug = 'default' ): array {
+ global $wpdb;
+ $query = '';
+ if ( 'with_variants' == $locale_slug ) {
+ $query = $wpdb->prepare(
+ "SELECT locale FROM {$wpdb->gp_translation_sets} WHERE `project_id` = %d",
+ 2 // 2 = wp/dev
+ );
+ } elseif ( 'default' == $locale_slug ) {
+ $query = $wpdb->prepare(
+ "SELECT locale FROM {$wpdb->gp_translation_sets} WHERE `project_id` = %d and slug = %s",
+ 2, // 2 = wp/dev
+ 'default'
+ );
+ }
+ if ( '' == $query ) {
+ return array();
+ }
+
+ return $wpdb->get_results( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ }
+
+ /**
+ * Returns the number of locales with WordPress core translation project generated.
+ *
+ * @return int
+ */
+ private function get_core_total(): int {
+ global $wpdb;
+
+ return $wpdb->get_results(
+ $wpdb->prepare(
+ "SELECT count(*) as counter from
+ (SELECT
+ (100 * stats.current/stats.all) as percent_complete
+ FROM {$wpdb->project_translation_status} stats
+ LEFT JOIN {$wpdb->gp_projects} projects ON stats.project_id = projects.id
+ WHERE
+ projects.path = 'wp/dev'
+ AND projects.active = 1
+ ) n"
+ )
+ )[0]->counter;
+ }
+
+ /**
+ * Returns the number of locales with WordPress full translated
+ *
+ * @return int
+ */
+ private function get_core_full_translated(): int {
+ global $wpdb;
+
+ return $wpdb->get_results(
+ $wpdb->prepare(
+ "SELECT count(*) as counter from
+ (SELECT
+ (100 * stats.current/stats.all) as percent_complete
+ FROM {$wpdb->project_translation_status} stats
+ LEFT JOIN {$wpdb->gp_projects} projects ON stats.project_id = projects.id
+ WHERE
+ projects.path = 'wp/dev'
+ AND projects.active = 1
+ HAVING
+ percent_complete >= 100.00) n"
+ )
+ )[0]->counter;
+ }
+
+ /**
+ * Returns the number of locales with WordPress core translation between the two values.
+ *
+ * @param int $upper_value
+ * @param int $lower_value
+ * @param string $minor_symbol
+ * @param string $greater_symbol
+ *
+ * @return int
+ */
+ private function get_core_interval( int $upper_value, int $lower_value, string $minor_symbol = '<', string $greater_symbol = '>=' ): int {
+ global $wpdb;
+
+ return $wpdb->get_results(
+ $wpdb->prepare(
+ "SELECT count(*) as counter from
+ (SELECT
+ (100 * stats.current/stats.all) as percent_complete
+ FROM {$wpdb->project_translation_status} stats
+ LEFT JOIN {$wpdb->gp_projects} projects ON stats.project_id = projects.id
+ WHERE
+ projects.path = 'wp/dev'
+ AND projects.active = 1
+ HAVING
+ percent_complete %1s %2d
+ AND percent_complete %3s %4d) n",
+ $greater_symbol,
+ $lower_value,
+ $minor_symbol,
+ $upper_value
+ )
+ )[0]->counter;
+ }
+
+ /**
+ * Returns the number of locales with WordPress empty translated.
+ *
+ * @return int
+ */
+ private function get_core_empty_translated(): int {
+ global $wpdb;
+
+ return $wpdb->get_results(
+ $wpdb->prepare(
+ "SELECT count(*) as counter from
+ (SELECT
+ (100 * stats.current/stats.all) as percent_complete
+ FROM {$wpdb->project_translation_status} stats
+ LEFT JOIN {$wpdb->gp_projects} projects ON stats.project_id = projects.id
+ WHERE
+ projects.path = 'wp/dev'
+ AND projects.active = 1
+ HAVING
+ percent_complete <= 0.00) n"
+ )
+ )[0]->counter;
+ }
+
+ /**
+ * Returns the first user registered in the current year.
+ *
+ * @return int
+ */
+ private function get_id_first_user_of_this_year(): int {
+ global $wpdb;
+ $user = $wpdb->get_row(
+ $wpdb->prepare(
+ "SELECT * FROM {$wpdb->users} where user_registered >= %s limit 1",
+ gmdate( 'Y-m-d H:i:s', strtotime( 'first day of january this year' ) )
+ )
+ );
+
+ return $user->ID;
+ }
+
+ /**
+ * Converts a property of an array of objects into a string with the properties of the
+ * different objects separated by a parameter
+ *
+ * @param array $objects Array of objects to convert.
+ * @param string $field Property of the object to convert.
+ * @param string $glue Text used to put between the fields.
+ *
+ * @return string
+ */
+ private function object_to_string( array $objects, string $field = 'user_id', string $glue = ', ' ): string {
+ $output = array();
+ if ( ! empty( $objects ) && count( $objects ) > 0 ) {
+ foreach ( $objects as $object ) {
+ if ( is_array( $object ) && isset( $object[ $field ] ) ) {
+ $output[] = $object[ $field ];
+ } elseif ( is_object( $object ) && isset( $object->$field ) ) {
+ $output[] = $object->$field;
+ }
+ }
+ }
+
+ return join( $glue, $output );
+ }
+
+ /**
+ * Gets the managers for the $role category for each locale.
+ *
+ * The role can be:
+ * - locale_manager.
+ * - general_translation_editor.
+ * - translation_editor.
+ *
+ * The $type parameter can be:
+ * - total: get all the contributors.
+ * - registered_this_year: get the users that have been registered this year and get the $role.
+ * - started_this_year: get the users that have start translating this year and get the $role.
+ *
+ * @param string $role Translator role.
+ * @param string $type Filter.
+ *
+ * @return array
+ */
+ private function count_managers( string $role, string $type = 'total' ): array {
+ global $wpdb;
+
+ $locales = get_locales();
+ $managers = array();
+ foreach ( $locales as $locale ) {
+ $result = get_sites(
+ array(
+ 'locale' => $locale->wp_locale,
+ 'network_id' => WPORG_GLOBAL_NETWORK_ID,
+ 'path' => '/',
+ 'fields' => 'ids',
+ 'number' => '1',
+ )
+ );
+ $site_id = array_shift( $result );
+ if ( ! $site_id ) {
+ continue;
+ }
+ $query = new \WP_User_Query();
+
+ $query->prepare_query(
+ array(
+ 'blog_id' => $site_id,
+ 'role' => $role,
+ )
+ );
+
+ if ( 'registered_this_year' === $type ) {
+ // Get the users that have registered this year.
+ $query->query_where .= ' AND user_id >= ' . $this->id_first_user_of_this_year;
+ }
+ if ( 'started_this_year' === $type ) {
+ // Get the users that have start translating this year.
+ $sql = "SELECT `user_id` FROM `translate_user_translations_count` WHERE `locale`='" .
+ $locale->slug . "' AND `accepted` > 0 AND `date_added` > '" . gmdate( 'Y' ) . "-01-01 00:00:00'";
+ $user_ids = $wpdb->get_results( $wpdb->prepare( $sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ $user_ids = $this->object_to_string( $user_ids );
+ $query->query_where .= ' AND user_id IN (' . $user_ids . ')';
+ }
+ $query->query();
+ $users = $query->get_results();
+
+ $managers[ $locale->english_name ] = count( $users );
+ }
+
+ return $managers;
+ }
+
+ /**
+ * Gets the translation contributors for the given locale.
+ *
+ * @return array
+ */
+ private function get_translation_contributors( GP_Locale $locale, $max_age_days = null ): array {
+ global $wpdb;
+
+ $contributors = array();
+
+ $date_constraint = '';
+ if ( null !== $max_age_days ) {
+ $date_constraint = $wpdb->prepare( ' AND date_modified >= CURRENT_DATE - INTERVAL %d DAY', $max_age_days );
+ }
+
+ [ $locale, $locale_slug ] = array_merge( explode( '/', $locale->slug ), array( 'default' ) );
+
+ $users = $wpdb->get_col(
+ $wpdb->prepare(
+ 'SELECT DISTINCT user_id FROM translate_user_translations_count WHERE accepted > 0 AND locale = %s AND locale_slug = %s',
+ $locale,
+ $locale_slug
+ ) . $date_constraint
+ );
+
+ if ( ! $users ) {
+ return $contributors;
+ }
+
+ $user_data = $wpdb->get_results( "SELECT user_nicename, display_name, user_email FROM $wpdb->users WHERE ID IN (" . implode( ',', $users ) . ')' );
+ foreach ( $user_data as $user ) {
+ if ( $user->display_name && $user->display_name !== $user->user_nicename ) {
+ $contributors[ $user->user_nicename ] = array(
+ 'display_name' => $user->display_name,
+ 'nice_name' => $user->user_nicename,
+ );
+ } else {
+ $contributors[ $user->user_nicename ] = array(
+ 'display_name' => $user->user_nicename,
+ 'nice_name' => $user->user_nicename,
+ );
+ }
+ }
+
+ uasort( $contributors, fn( $a, $b ) => strnatcasecmp( $a['display_name'], $b['display_name'] ) );
+
+ return $contributors;
+ }
+
+ /**
+ * Type:
+ * - topic
+ * - reply
+ *
+ * @param string $type Type of query: topic or reply.
+ * @param int|null $year Year fot the stats.
+ * @return array
+ */
+ private function get_forums_stats( string $type, int $year = null ): array {
+ global $wpdb;
+
+ $date_constraint = '';
+ if ( null !== $year ) {
+ $date_constraint = $wpdb->prepare( ' AND YEAR(post_date_gmt) = ' . $year ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ }
+ foreach ( $this->forum_ids as $key => $value ) {
+ global $wpdb;
+ $sql = "SELECT COUNT(id) as posts FROM wporg_{$value}_posts WHERE post_status='publish' AND post_type='{$type}'";
+ $site_posts = $wpdb->get_row(
+ $wpdb->prepare(
+ $sql, // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ ) . $date_constraint
+ );
+
+ $posts[ $key ] = $site_posts->posts;
+ }
+
+ return $posts;
+ }
+
+ /**
+ * Update the page with the Polyglots stats.
+ *
+ * All the previous information is deleted.
+ * The page updated is https://make.wordpress.org/polyglots/stats.
+ *
+ * @return void
+ */
+ private function update_page() {
+ define( 'MAKE_POLYGLOTS_BLOG_ID', 19 );
+ define( 'POLYGLOTS_PAGE_ID', 42132 );
+ add_filter( 'wp_revisions_to_keep',
+ function ( $num, $post ) {
+ if ( POLYGLOTS_PAGE_ID === $post->ID ) {
+ $num = 0; // pretend we don't want to keep revisions so that it will not lookup all old revisions.
+ }
+
+ return $num;
+ },
+ 10,
+ 2 );
+ switch_to_blog( MAKE_POLYGLOTS_BLOG_ID );
+
+ $ret = wp_update_post( array(
+ 'ID' => POLYGLOTS_PAGE_ID,
+ 'post_type' => 'page',
+ 'post_author' => 'Amieiro',
+ 'post_content' => $this->get_polyglots_stats_page_content(),
+ ), true ); // phpcs:ignore PEAR.Functions.FunctionCallSignature.MultipleArguments
+ restore_current_blog();
+ if ( $ret ) {
+ print_wpcli_heading( 'Page updated! https://make.wordpress.org/polyglots/stats/');
+ } else {
+ $this->print_wpcli_heading('We had a problem updating the page https://make.wordpress.org/polyglots/stats/');
+ }
+ }
+
+ /**
+ * Return the content for the web page, concatenating some strings.
+ *
+ * @return string
+ */
+ private function get_polyglots_stats_page_content(): string {
+ return $this->header .
+ $this->wordpress_translation_percentage .
+ $this->originals_by_year .
+ $this->packages_generated_by_year .
+ $this->themes_plugins_by_year .
+ $this->translations_translators_by_year .
+ $this->forum_post_and_replies_by_year .
+ $this->feedback_received .
+ $this->contributors_per_locale .
+ $this->managers_stats .
+ $this->most_active_translators;
+ }
+
+ /**
+ * Create a Gutenberg heading.
+ *
+ * @param string $text_to_insert
+ * @param string $header_type
+ *
+ * @return string
+ */
+ private function create_gutenberg_heading( string $text_to_insert, string $header_type = 'h1' ): string {
+ $heading = '<!-- wp:heading -->';
+ $heading .= '<' . $header_type . '>' . $text_to_insert . '</' . $header_type . '>';
+ $heading .= '<!-- /wp:heading -->';
+
+ return $heading;
+ }
+
+ /**
+ * Print a WP-CLI heading.
+ *
+ * @param string $text_to_print
+ *
+ * @return void
+ */
+ private function print_wpcli_heading( string $text_to_print ): void {
+ WP_CLI::log( '' );
+ WP_CLI::log( '----------------------------------------------------------------' );
+ WP_CLI::log( $text_to_print );
+ WP_CLI::log( '----------------------------------------------------------------' );
+ }
+
+ /**
+ * Create a Gutenberg paragraph.
+ *
+ * @param string $text_to_insert
+ *
+ * @return string
+ */
+ private function create_gutenberg_paragraph( string $text_to_insert ): string {
+ $paragraph = '<!-- wp:paragraph -->';
+ $paragraph .= '<p>' . $text_to_insert . '<p>';
+ $paragraph .= '<!-- /wp:paragraph -->';
+
+ return $paragraph;
+ }
+
+ /**
+ * Create a Gutenberg paragraph.
+ *
+ * @param string $text_to_insert
+ *
+ * @return string
+ */
+ private function create_gutenberg_code( string $text_to_insert ): string {
+ $code = '<!-- wp:code -->';
+ $code .= '<pre class="wp-block-code"><code>';
+ $code .= '<p>' . $text_to_insert . '<p>';
+ $code .= '</code></pre>';
+ $code .= '<!-- /wp:code -->';
+
+ return $code;
+ }
+}
</ins><span class="cx" style="display: block; padding: 0 10px">Property changes on: sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli/class-show-stats.php
</span><span class="cx" style="display: block; padding: 0 10px">___________________________________________________________________
</span></span></pre></div>
<a id="svneolstyle"></a>
<div class="addfile"><h4 style="background-color: #eee; color: inherit; margin: 1em 0; padding: 1.3em; font-size: 115%">Added: svn:eol-style</h4></div>
<ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+native
</ins><span class="cx" style="display: block; padding: 0 10px">\ No newline at end of property
</span></div>
</body>
</html>