set_id('rating'); $tabsheet->add('rating', l10n('Photos'), get_root_url().'admin.php?page=rating'); $tabsheet->add('rating_user', l10n('Users'), get_root_url().'admin.php?page=rating_user'); $tabsheet->select('rating_user'); $tabsheet->assign(); $filter_min_rates = 2; if (isset($_GET['f_min_rates'])) { $filter_min_rates = (int)$_GET['f_min_rates']; } // build users global $conf; $query = 'SELECT DISTINCT u.'.$conf['user_fields']['id'].' AS id, u.'.$conf['user_fields']['username'].' AS name, ui.status FROM '.USERS_TABLE.' AS u INNER JOIN '.USER_INFOS_TABLE.' AS ui ON u.'.$conf['user_fields']['id'].' = ui.user_id'; $users_by_id = array(); $result = pwg_query($query); while ($row = pwg_db_fetch_assoc($result)) { $users_by_id[(int)$row['id']] = array( 'name' => $row['name'], 'anon' => is_autorize_status(ACCESS_CLASSIC, $row['status']) ? false : true ); } $by_user_rating_model = array( 'rates' => array() ); foreach($conf['rate_items'] as $rate) { $by_user_rating_model['rates'][$rate] = array(); } $image_ids = array(); $by_user_ratings = array(); $query = ' SELECT * FROM '.RATE_TABLE.' ORDER by date DESC'; $result = pwg_query($query); while ($row = pwg_db_fetch_assoc($result)) { if (!isset($users_by_id[$row['user_id']])) { $users_by_id[$row['user_id']] = array('name' => '???'.$row['user_id'], 'anon' => false); } $usr = $users_by_id[$row['user_id']]; if ($usr['anon']) { $user_key = $usr['name'].'('.$row['anonymous_id'].')'; } else { $user_key = $usr['name']; } $rating = & $by_user_ratings[$user_key]; if ( is_null($rating) ) { $rating = $by_user_rating_model; $rating['uid'] = (int)$row['user_id']; $rating['aid'] = $usr['anon'] ? $row['anonymous_id'] : ''; } $rating['rates'][$row['rate']][] = array( 'id' => $row['element_id'], 'date' => $row['date'], ); $image_ids[$row['element_id']] = 1; unset($rating); } // get image tn urls $image_urls = array(); if (count($image_ids) > 0 ) { $query = 'SELECT id, name, file, path, representative_ext FROM '.IMAGES_TABLE.' WHERE id IN ('.implode(',', array_keys($image_ids)).')'; $result = pwg_query($query); while ($row = pwg_db_fetch_assoc($result)) { $image_urls[ $row['id'] ] = array( 'tn' => DerivativeImage::thumb_url($row), 'page' => make_picture_url( array('image_id'=>$row['id'], 'image_file'=>$row['file']) ), ); } } $query='SELECT element_id, AVG(rate) AS avg FROM '.RATE_TABLE.' GROUP BY element_id'; $all_img_sum = array(); $result = pwg_query($query); while ($row = pwg_db_fetch_assoc($result)) { $all_img_sum[(int)$row['element_id']] = array( 'avg'=>(float)$row['avg'] ); } foreach($by_user_ratings as $id => &$rating) { $c=0; $s=0; $ss=0; $consensus_dev=0; foreach($rating['rates'] as $rate => $rates) { $ct = count($rates); $c += $ct; $s += $ct * $rate; $ss += $ct * $rate * $rate; foreach($rates as $id_date) { $consensus_dev += abs($rate - $all_img_sum[$id_date['id']]['avg']); } } $consensus_dev /= $c; $var = ($ss - $s*$s/$c)/$c; $rating += array( 'id' => $id, 'count' => $c, 'avg' => $s/$c, 'cv' => $s==0 ? -1 : sqrt($var)/($s/$c), // http://en.wikipedia.org/wiki/Coefficient_of_variation 'cd' => $consensus_dev ); } unset($rating); // filter foreach($by_user_ratings as $id => $rating) { if ($rating['count'] <= $filter_min_rates) { unset($by_user_ratings[$id]); } } function avg_compare($a, $b) { $d = $a['avg'] - $b['avg']; return ($d==0) ? 0 : ($d<0 ? -1 : 1); } function count_compare($a, $b) { $d = $a['count'] - $b['count']; return ($d==0) ? 0 : ($d<0 ? -1 : 1); } function cv_compare($a, $b) { $d = $b['cv'] - $a['cv']; //desc return ($d==0) ? 0 : ($d<0 ? -1 : 1); } function consensus_dev_compare($a, $b) { $d = $b['cd'] - $a['cd']; //desc return ($d==0) ? 0 : ($d<0 ? -1 : 1); } $order_by_index=3; if (isset($_GET['order_by']) and is_numeric($_GET['order_by'])) { $order_by_index = $_GET['order_by']; } $available_order_by= array( array(l10n('Average rate'), 'avg_compare'), array(l10n('Number of rates'), 'count_compare'), array(l10n('Variation'), 'cv_compare'), array(l10n('Consensus deviation'), 'consensus_dev_compare'), ); for ($i=0; $iappend( 'order_by_options', $available_order_by[$i][0] ); } $template->assign('order_by_options_selected', array($order_by_index) ); $x = uasort($by_user_ratings, $available_order_by[$order_by_index][1] ); $template->assign( array( 'F_ACTION' => get_root_url().'admin.php', 'F_MIN_RATES' => $filter_min_rates, 'available_rates' => $conf['rate_items'], 'ratings' => $by_user_ratings, 'image_urls' => $image_urls, 'TN_WIDTH' => 28+2*ImageStdParams::get_by_type(IMG_THUMB)->sizing->ideal_size[0], ) ); $template->set_filename('rating', 'rating_user.tpl'); $template->assign_var_from_handle('ADMIN_CONTENT', 'rating'); ?>