merge with snvdevel

3 files changed, 212 insertions, 131 deletions

diff --git a/bin/compatibility_report.php b/bin/compatibility_report.php
index 1ad2ceb..567139e 100644
--- a/bin/compatibility_report.php
+++ b/bin/compatibility_report.php
@@ -52,39 +52,6 @@ class Object {}
 $db = new Database();
 $versioncompare = new VersioncompareComponent();
 
-global $compatibility_versions;
-
-$appversions = array();
-$totals = array();
-
-foreach ($compatibility_versions as $compatibility_version) {
-    $totals[$compatibility_version] = array(
-        COMPAT_LATEST => array(
-            'count' => 0,
-            'adu' => 0
-        ),
-        COMPAT_BETA => array(
-            'count' => 0,
-            'adu' => 0
-        ),
-        COMPAT_ALPHA => array(
-            'count' => 0,
-            'adu' => 0
-        ),
-        COMPAT_OTHER => array(
-            'count' => 0,
-            'adu' => 0
-        )
-    );
-    
-    $versions_qry = $db->query("SELECT id, version FROM appversions WHERE application_id = ".APP_FIREFOX." AND version LIKE '{$compatibility_version}%'");
-    while ($version = mysql_fetch_assoc($versions_qry)) {
-        $versions[$compatibility_version][$version['id']] = $version['version'];
-    }
-    
-    $appversions[$compatibility_version] = $versioncompare->getCompatibilityGrades($compatibility_version, $versions[$compatibility_version]);
-}
-
 // Get latest update pings date
 $date_qry = $db->query("SELECT date FROM update_counts ORDER BY date DESC LIMIT 1");
 $date_array = mysql_fetch_assoc($date_qry);
@@ -112,71 +79,116 @@ $addon_qry = $db->query("
                 translations.locale = 'en-US' AND
                 versions.id = (
                     SELECT id FROM versions WHERE addon_id = addons.id ORDER BY created DESC LIMIT 1
-                ) AND
-                addons.id NOT IN (1269,2390,10,4938)
+                )
             GROUP BY
                 addons.id
             ORDER BY
                 update_counts.count DESC
         ");
 
-$adu_total = 0;
 $all_addons = array();
-$addons = array();
 // Sum all update pings to establish total active users
 while ($addon = mysql_fetch_assoc($addon_qry)) {
-    $adu_total += $addon['updatepings'];
     $all_addons[] = $addon;
 }
 
-// Calculate 95%
-$adu_top95 = floor($adu_total * .95);
-$adu_counter = 0;
+// set in site/app/config/config.php
+global $compatibility_versions;
 
-// Iterate through each add-on
-foreach ($all_addons as $addon) {
-    // Only include add-ons that make up the top 95%
-    if ($adu_counter >= $adu_top95) break;
-    
-    $classification = array();
-    
-    foreach ($compatibility_versions as $compatibility_version) {
-        $classification[$compatibility_version] = $versioncompare->gradeCompatibility($addon['maxversion'], $compatibility_version, $appversions[$compatibility_version]);
+// Previous version defaults to 2.0 because 3.0 is the first compat version we have available
+$previous_version = '2.0';
 
-        $totals[$compatibility_version][$classification[$compatibility_version]]['count']++;
-        $totals[$compatibility_version][$classification[$compatibility_version]]['adu'] += $addon['updatepings'];
+/**
+ * iterate through each major compatibility version and make an individual
+ * report based on above general info
+ */
+foreach ($compatibility_versions as $compatibility_version) {
+    $compat_addons = array();
+    $adu_total = 0;
+    foreach ($all_addons as $addon) {
+        // Only count this add-on if it is compatible with the major/minor release before
+        if (!empty($previous_version) && $versioncompare->compareVersions($addon['maxversion'], $previous_version) < 0) continue;
+        
+        $compat_addons[] = $addon;
+        $adu_total += $addon['updatepings'];
     }
     
-    $addons[] = array(
-        'id' => $addon['id'],
-        'name' => $addon['name'],
-        'maxversion' => $addon['maxversion'],
-        'featured' => $addon['featured'],
-        'percentage' => ($addon['updatepings'] / $adu_top95),
-        'classification' => $classification
+    $adu_top95 = floor($adu_total * .95);
+    
+    $totals = array(
+        COMPAT_LATEST => array(
+            'count' => 0,
+            'adu' => 0
+        ),
+        COMPAT_BETA => array(
+            'count' => 0,
+            'adu' => 0
+        ),
+        COMPAT_ALPHA => array(
+            'count' => 0,
+            'adu' => 0
+        ),
+        COMPAT_OTHER => array(
+            'count' => 0,
+            'adu' => 0
+        )
     );
     
-    //echo "{$addon['name']} - {$addon['maxversion']} - {$classification['3.1']} - {$adu_counter}<br>";
-
-    $adu_counter += $addon['updatepings'];
-}
-
-$totals['adu95'] = $adu_top95;
-$totals['adu'] = $adu_total;
-$totals['addons95'] = count($addons);
-$totals['addons'] = mysql_num_rows($addon_qry);
+    $versions_qry = $db->query("SELECT id, version FROM appversions WHERE application_id = ".APP_FIREFOX." AND version LIKE '{$compatibility_version}%'");
+    $versions = array();
+    while ($version = mysql_fetch_assoc($versions_qry)) {
+        $versions[$version['id']] = $version['version'];
+    }
+    
+    $appversions = $versioncompare->getCompatibilityGrades($compatibility_version, $versions);
+    
+    
+    $adu_counter = 0;
+    $addons = array();
+    
+    // Iterate through each add-on
+    foreach ($compat_addons as $addon) {
+        // Only include add-ons that make up the top 95%
+        if ($adu_counter >= $adu_top95) break;
+        
+        $classification = $versioncompare->gradeCompatibility($addon['maxversion'], $compatibility_version, $appversions);
+        
+        $totals[$classification]['count']++;
+        $totals[$classification]['adu'] += $addon['updatepings'];
+        
+        $addons[] = array(
+            'id' => $addon['id'],
+            'name' => $addon['name'],
+            'maxversion' => $addon['maxversion'],
+            'featured' => $addon['featured'],
+            'percentage' => ($addon['updatepings'] / $adu_top95),
+            'classification' => $classification
+        );
+        
+        $adu_counter += $addon['updatepings'];
+    }
+    
+    $totals['adu95'] = $adu_top95;
+    $totals['adu'] = $adu_total;
+    $totals['addons95'] = count($addons);
+    $totals['addons'] = mysql_num_rows($addon_qry);
+    
+    echo "<br /><hr />Report for Firefox {$compatibility_version}<br />";
+    echo "Using data from {$latest_date}<br />";
+    echo "{$totals['adu']} total active users; {$totals['adu95']} making up top 95%<br />";
+    echo "{$totals['addons']} rows returned; {$totals['addons95']} addons counted<br /><br />";
+
+    $output = array(
+        'addons' => $addons,
+        'totals' => $totals,
+        'appversions' => $appversions
+    );
 
-echo "{$latest_date}<br />";
-echo "{$totals['adu']} total active users; {$totals['adu95']} making up top 95%<br />";
-echo "{$totals['addons']} rows returned; {$totals['addons95']} addons counted<br /><br />";
+    echo '<pre>'.print_r($output, true).'</pre>';
 
-$output = array(
-    'addons' => $addons,
-    'totals' => $totals,
-    'appversions' => $appversions
-);
+    file_put_contents(NETAPP_STORAGE.'/compatibility-fx-'.$compatibility_version.'.serialized', serialize($output));
 
-echo '<pre>'.print_r($output, true).'</pre>';
+    $previous_version = $compatibility_version;
+}
 
-file_put_contents(NETAPP_STORAGE.'/compatibility.serialized', serialize($output));
 ?>
diff --git a/bin/maintenance.php b/bin/maintenance.php
index 811421d..8269fe8 100755
--- a/bin/maintenance.php
+++ b/bin/maintenance.php
@@ -172,8 +172,6 @@ switch ($action) {
                 $total_counts[$row['addon_id']] = ($row['total_count'] > 0) ? $row['total_count'] : 0;
             }
 
-            echo 'Updating seven day counts in `main` ...'."\n";
-
             foreach ($total_counts as $id => $total_count) {
                 $total_count_update_sql = "
                     UPDATE `addons` SET `totaldownloads`='{$total_count}' WHERE `id`='{$id}'
@@ -268,26 +266,22 @@ switch ($action) {
         echo 'Starting review total updates...'."\n";
         
         $reviews_sql = "
-            SELECT
-                versions.addon_id as addon_id,
-                count(DISTINCT(reviews.user_id)) as count 
-            FROM reviews 
-            INNER JOIN versions ON reviews.version_id = versions.id 
-            WHERE reviews.reply_to IS NULL
-            GROUP BY versions.addon_id 
+            UPDATE addons AS a
+            INNER JOIN (
+                SELECT
+                    versions.addon_id as addon_id,
+                    COUNT(*) as count
+                FROM reviews 
+                INNER JOIN versions ON reviews.version_id = versions.id 
+                WHERE reviews.reply_to IS NULL
+                    AND reviews.rating > 0
+                GROUP BY versions.addon_id 
+            ) AS c ON (a.id = c.addon_id)
+            SET a.totalreviews = c.count
         ";
-
         $reviews_result = $db->query($reviews_sql);
 
-        $affected_rows = mysql_num_rows($reviews_result);
-
-        while ($row = mysql_fetch_array($reviews_result)) {
-            if ($db->query("UPDATE addons SET totalreviews = {$row['count']} WHERE id = {$row['addon_id']}", true)) {
-                echo "Review total updated to {$row['count']} updated for addon {$row['addon_id']}\n";
-            } else {
-                echo "Review total update failed for addon {$row['addon_id']}\n";
-            }
-        }
+        $affected_rows = mysql_affected_rows($reviews_result);
 
     break;
 
@@ -297,45 +291,65 @@ switch ($action) {
      * Get average ratings and update addons table.
      */
     case 'ratings':
-        echo 'Starting average ratings updates...'."\n";
-        $avg_sql = "SELECT SUM(rating)/count(rating) as avg from reviews WHERE rating > 0 AND reply_to IS NULL";
-        $avg_result = $db->query($avg_sql);
-        $row= mysql_fetch_array($avg_result);
-        $_smoothfunc = $row['avg'];
-
+        echo 'Updating average ratings...'."\n";
         $rating_sql = "
-            SELECT 
-                addon_id,(sum(latest_rating)+$_smoothfunc)/(count(latest_rating) + 1) AS avg
-            FROM (
-                    SELECT 
-                        v.addon_id,
-                        SUBSTRING_INDEX( GROUP_CONCAT( r.rating ORDER BY r.created DESC SEPARATOR ';'), ';', 1 ) AS latest_rating,
-                        SUBSTRING_INDEX( GROUP_CONCAT( r.created ORDER BY r.created DESC SEPARATOR ';'), ';', 1 ) AS latest_created
-                    FROM 
-                        reviews r
-                    INNER JOIN 
-                        versions v ON r.version_id = v.id 
-                    WHERE 
-                        r.rating > 0 AND 
-                        r.reply_to IS NULL
-                    GROUP BY r.user_id 
-                    ORDER BY latest_created DESC 
-                ) AS t1
-            GROUP BY addon_id
+            UPDATE addons AS a
+            INNER JOIN (
+                SELECT
+                    versions.addon_id as addon_id,
+                    AVG(rating) as avg_rating
+                FROM reviews 
+                INNER JOIN versions ON reviews.version_id = versions.id 
+                WHERE reviews.reply_to IS NULL
+                    AND reviews.rating > 0
+                GROUP BY versions.addon_id 
+            ) AS c ON (a.id = c.addon_id)
+            SET a.averagerating = ROUND(c.avg_rating, 2)
         ";
-
         $rating_result = $db->query($rating_sql);
 
-        $affected_rows = mysql_num_rows($rating_result);
+        echo 'Updating bayesian ratings...'."\n";
+        // get average review count and average rating
+        $rows = $db->query("
+            SELECT AVG(a.cnt) AS avg_cnt 
+            FROM (
+                SELECT COUNT(*) AS cnt
+                FROM reviews AS r
+                INNER JOIN versions AS v ON (r.version_id = v.id)
+                WHERE reply_to IS NULL
+                    AND rating > 0
+                GROUP BY v.addon_id
+                ) AS a
+        ");
+        $row = mysql_fetch_array($rows);
+        $avg_num_votes = $row['avg_cnt'];
+        
+        $rows = $db->query("
+            SELECT AVG(a.addon_rating) AS avg_rating 
+            FROM (
+                SELECT AVG(rating) AS addon_rating
+                FROM reviews AS r
+                INNER JOIN versions AS v ON (r.version_id = v.id)
+                WHERE reply_to IS NULL
+                    AND rating > 0
+                GROUP BY v.addon_id
+                ) AS a
+        ");
+        $row = mysql_fetch_array($rows);
+        $avg_rating = $row['avg_rating'];
+        
+        // calculate and store bayesian rating
+        $rating_sql = "
+            UPDATE addons AS a
+            SET a.bayesianrating =
+                IF (a.totalreviews > 0, (
+                    ( ({$avg_num_votes} * {$avg_rating}) + (a.totalreviews * a.averagerating) ) /
+                    ({$avg_num_votes} + a.totalreviews)
+                ), 0)
+        ";
+        $rating_result = $db->query($rating_sql);
 
-        while ($row = mysql_fetch_array($rating_result)) {
-            $_avg = round($row['avg'],2);
-            if ($db->query("UPDATE addons SET averagerating = '{$_avg}' WHERE id = {$row['addon_id']}", true)) {
-                echo "Rating average updated to {$_avg} updated for addon {$row['addon_id']}\n";
-            } else {
-                echo "Rating average update failed for addon {$row['addon_id']}\n";
-            }
-        }
+        $affected_rows = mysql_affected_rows();
 
     break;
 
@@ -360,6 +374,60 @@ switch ($action) {
 
 
     /**
+     * Update addon-collection download totals.
+     */
+    case 'addons_collections_total':
+        echo "Starting addon_collection totals update...\n";
+        $addons_collections_total = "
+            UPDATE
+                addons_collections AS ac
+            INNER JOIN (
+                SELECT
+                    stats.addon_id as addon_id,
+                    stats.collection_id as collection_id,
+                    SUM(stats.count) AS sum
+                FROM
+                    stats_addons_collections_counts AS stats
+                GROUP BY
+                    stats.addon_id, stats.collection_id
+            ) AS j ON (ac.addon_id = j.addon_id AND
+                       ac.collection_id = j.collection_id)
+            SET
+                ac.downloads = j.sum
+        ";
+        $db->query($addons_collections_sql);
+        $affected_rows = mysql_affected_rows();
+    break;
+
+
+
+    /**
+     * Update collection downloads total.
+     */
+    case 'collections_total':
+        echo "Starting collection totals update...\n";
+        $collections_sql = "
+            UPDATE
+                collections AS c
+            INNER JOIN (
+                SELECT
+                    stats.collection_id AS collection_id,
+                    SUM(stats.count) AS sum
+                FROM
+                    stats_collections_counts AS stats
+                GROUP BY
+                    stats.collection_id
+            ) AS j ON (c.id = j.collection_id)
+            SET
+                c.downloads = j.sum
+        ";
+        $db->query($collections_sql);
+        $affected_rows = mysql_affected_rows();
+    break;
+
+
+
+    /**
      * Unknown command.
      */
     default:
diff --git a/bin/parse_logs/count_downloads.class.php b/bin/parse_logs/count_downloads.class.php
index 9db7d61..f1e43f1 100644
--- a/bin/parse_logs/count_downloads.class.php
+++ b/bin/parse_logs/count_downloads.class.php
@@ -73,6 +73,7 @@ class Count_Downloads {
         if (isset($this->countedIPs[$details['ip']])) {
             $this->totalSkipped['blacklist']++;
             outputIfVerbose("[DownloadCounter] IP ({$details['ip']}) in blacklist; skipped");
+            return;
         }
 
         $_addon_ids = array();