feature 2384: improve average rating calculation (still need to update language files)

git-svn-id: http://piwigo.org/svn/trunk@11827 68402e56-0260-453c-a942-63ccdbb3a9ee

7 files changed, 17 insertions, 73 deletions

diff --git a/admin/configuration.php b/admin/configuration.php
index 82c16b8e7..f22b0f4fb 100644
--- a/admin/configuration.php
+++ b/admin/configuration.php
@@ -109,7 +109,7 @@ $sort_fields = array(
   'file' => l10n('File name'),
   'date_creation' => l10n('Creation date'),
   'date_available' => l10n('Post date'),
-  'average_rate' => l10n('Average rate'),
+  'average_rate' => l10n('Rating score'),
   'hit' => l10n('Most visited'),
   'id' => 'Id',
   );
diff --git a/admin/element_set_ranks.php b/admin/element_set_ranks.php
index 05576777e..96084f2d1 100644
--- a/admin/element_set_ranks.php
+++ b/admin/element_set_ranks.php
@@ -273,7 +273,7 @@ $sort_fields = array(
   '' => '',
   'date_creation' => l10n('Creation date'),
   'date_available' => l10n('Post date'),
-  'average_rate' => l10n('Average rate'),
+  'average_rate' => l10n('Rate score'),
   'hit' => l10n('Most visited'),
   'file' => l10n('File name'),
   'id' => 'Id',
diff --git a/admin/include/functions.php b/admin/include/functions.php
index cd03a662e..2c9c9df39 100644
--- a/admin/include/functions.php
+++ b/admin/include/functions.php
@@ -1051,67 +1051,6 @@ UPDATE '.IMAGES_TABLE.'
 }
 
 /**
- * update images.average_rate field
- * param int $element_id optional, otherwise applies to all
- * @return void
- */
-function update_average_rate( $element_id=-1 )
-{
-  $query = '
-SELECT element_id,
-       ROUND(AVG(rate),2) AS average_rate
-  FROM '.RATE_TABLE;
-  if ( $element_id != -1 )
-  {
-    $query .= ' WHERE element_id=' . $element_id;
-  }
-  $query .= ' GROUP BY element_id;';
-
-  $result = pwg_query($query);
-
-  $datas = array();
-
-  while ($row = pwg_db_fetch_assoc($result))
-  {
-    array_push(
-      $datas,
-      array(
-        'id' => $row['element_id'],
-        'average_rate' => $row['average_rate']
-        )
-      );
-  }
-
-  mass_updates(
-    IMAGES_TABLE,
-    array(
-      'primary' => array('id'),
-      'update' => array('average_rate')
-      ),
-    $datas
-    );
-
-  $query='
-SELECT id FROM '.IMAGES_TABLE .'
-  LEFT JOIN '.RATE_TABLE.' ON id=element_id
-  WHERE element_id IS NULL AND average_rate IS NOT NULL';
-  if ( $element_id != -1 )
-  {
-    $query .= ' AND id=' . $element_id;
-  }
-  $to_update = array_from_query( $query, 'id');
-
-  if ( !empty($to_update) )
-  {
-    $query='
-UPDATE '.IMAGES_TABLE .'
-  SET average_rate=NULL
-  WHERE id IN (' . implode(',',$to_update) . ')';
-    pwg_query($query);
-  }
-}
-
-/**
  * change the parent category of the given categories. The categories are
  * supposed virtual.
  *
diff --git a/admin/maintenance.php b/admin/maintenance.php
index 1199af948..886739efc 100644
--- a/admin/maintenance.php
+++ b/admin/maintenance.php
@@ -58,7 +58,8 @@ switch ($action)
   case 'images' :
   {
     update_path();
-    update_average_rate();
+		include_once(PHPWG_ROOT_PATH.'include/functions_rate.inc.php');
+    update_rating_score();
     break;
   }
   case 'delete_orphan_tags' :
diff --git a/admin/rating.php b/admin/rating.php
index 3b06c4732..339f3044a 100644
--- a/admin/rating.php
+++ b/admin/rating.php
@@ -85,7 +85,7 @@ AND user_id=' . $vars['u'] . '
 AND anonymous_id=\'' . $vars['a'] . '\'
 ;';
   pwg_query($query);
-  update_average_rate( $vars['e'] );
+  update_rating_score( $vars['e'] );
 }
 
 $users = array();
@@ -100,10 +100,9 @@ while ($row = pwg_db_fetch_assoc($result))
 }
 
 
-$query = 'SELECT COUNT(DISTINCT(i.id))
-FROM '.RATE_TABLE.' AS r, '.IMAGES_TABLE.' AS i
-WHERE r.element_id=i.id'. $page['user_filter'] .
-';';
+$query = 'SELECT COUNT(DISTINCT(r.element_id))
+FROM '.RATE_TABLE.' AS r
+WHERE 1=1'. $page['user_filter'];
 list($nb_images) = pwg_db_fetch_row(pwg_query($query));
 
 
@@ -131,7 +130,8 @@ $template->assign(
 
 $available_order_by= array(
     array(l10n('Rate date'), 'recently_rated DESC'),
-    array(l10n('Average rate'), 'average_rate DESC'),
+		array(l10n('Rating score'), 'score DESC'),
+    array(l10n('Average rate'), 'avg_rates DESC'),
     array(l10n('Number of rates'), 'nb_rates DESC'),
     array(l10n('Sum of rates'), 'sum_rates DESC'),
     array(l10n('File name'), 'file DESC'),
@@ -164,8 +164,9 @@ SELECT i.id,
        i.path,
        i.file,
        i.tn_ext,
-       i.average_rate,
+       i.average_rate				AS score,
        MAX(r.date)          AS recently_rated,
+			 ROUND(AVG(r.rate),2) AS avg_rates,
        COUNT(r.rate)        AS nb_rates,
        SUM(r.rate)          AS sum_rates
   FROM '.RATE_TABLE.' AS r
@@ -207,7 +208,8 @@ ORDER BY date DESC;';
      array(
        'U_THUMB' => $thumbnail_src,
        'U_URL' => $image_url,
-       'AVG_RATE' => $image['average_rate'],
+			 'SCORE_RATE' => $image['score'],
+       'AVG_RATE' => $image['avg_rates'],
        'SUM_RATE' => $image['sum_rates'],
        'NB_RATES' => (int)$image['nb_rates'],
        'NB_RATES_TOTAL' => (int)$nb_rates,
diff --git a/admin/themes/default/template/configuration.tpl b/admin/themes/default/template/configuration.tpl
index 85574ed4b..9162cad18 100644
--- a/admin/themes/default/template/configuration.tpl
+++ b/admin/themes/default/template/configuration.tpl
@@ -415,7 +415,7 @@ jQuery(document).ready(function () {
 
     <li>
       <label>
-        <span class="property">{'Average rate'|@translate}</span>
+        <span class="property">{'Rating score'|@translate}</span>
         <input type="checkbox" name="picture_informations[average_rate]" {if ($display.picture_informations.average_rate)}checked="checked"{/if}>
       </label>
     </li>
diff --git a/admin/themes/default/template/rating.tpl b/admin/themes/default/template/rating.tpl
index 0c001437a..a6d107a69 100644
--- a/admin/themes/default/template/rating.tpl
+++ b/admin/themes/default/template/rating.tpl
@@ -37,6 +37,7 @@
 <tr class="throw">
   <td>{'File'|@translate}</td>
   <td>{'Number of rates'|@translate}</td>
+	<td>{'Rating score'|@translate}</td>
   <td>{'Average rate'|@translate}</td>
   <td>{'Sum of rates'|@translate}</td>
   <td>{'Rate'|@translate}</td>
@@ -48,6 +49,7 @@
 <tr valign="top" class="{if $smarty.foreach.image.index is odd}row1{else}row2{/if}">
   <td rowspan="{$image.NB_RATES_TOTAL+1}"><a href="{$image.U_URL}"><img src="{$image.U_THUMB}" alt="{$image.FILE}" title="{$image.FILE}"></a></td>
   <td rowspan="{$image.NB_RATES_TOTAL+1}"><strong>{$image.NB_RATES}/{$image.NB_RATES_TOTAL}</strong></td>
+	<td rowspan="{$image.NB_RATES_TOTAL+1}"><strong>{$image.SCORE_RATE}</strong></td>
   <td rowspan="{$image.NB_RATES_TOTAL+1}"><strong>{$image.AVG_RATE}</strong></td>
   <td rowspan="{$image.NB_RATES_TOTAL+1}" style="border-right: 1px solid;" ><strong>{$image.SUM_RATE}</strong></td>
 </tr>