unc_ranking.inc.php

<?php

/**
 *  Show the images for ranking
 */
function unc_ranking_show() {
    echo "Pick the image you would like to see in a book about life music in HKG. "
        . "Ask yourself if the image is conveying the spirit of \"Hong Kong Rocks\"? Please note that if you vote for one image, the other can still be voted up in comparison with other images!";

    $rank_year = filter_input(INPUT_POST, 'rank_year', FILTER_VALIDATE_INT);

    if (!$rank_year) {
        $rank_year = 2020;
    }

    echo "<form method=\"POST\"> Please pick a year: " . unc_ranking_datepicker($rank_year) . "</form>";

    echo "<div id=\"ranking_imagebox\">";
    unc_ranking_new_image(true, $rank_year);
    echo "</div>";

    echo "Can't decide? <a href=\"https://hongkong-rocks.com/picture-ranking/\">Click here to get a new set</a>";
}

/**
 * shows a new set of images, from the inital page build or from AJAX
 *
 * @global type $wpdb
 * @param type $rank_year
 */
function unc_ranking_new_image($php = false, $rank_year = 2020) {

    if (!$php) {
        ob_clean();
    }

    // submit the new ranking
    unc_ranking_update();

    global $wpdb;

    $rank_year_get = filter_input(INPUT_GET, 'rank_year', FILTER_VALIDATE_INT);
    if ($rank_year_get) {
        $rank_year = $rank_year_get;
    }

    $i = unc_ranking_select($rank_year);

    $img_table = $wpdb->prefix . "unc_gallery_att";

    $sql = "SELECT file_id, att_value FROM $img_table
        WHERE file_id IN ({$i['images'][0]},{$i['images'][1]})
        AND att_group LIKE 'default'
        AND att_name='file_url'";

    $I = $wpdb->get_results($sql, 'ARRAY_A');

    $out = "
    Click on the image you prefer:
    <div style=\"padding:10px;\">
        <img class=\"ranking\" src=\"{$I[0]['att_value']}\" id=\"ranking_left\">
        <img class=\"ranking\" src=\"{$I[1]['att_value']}\" id=\"ranking_right\">
        <script>
            jQuery(\"#ranking_left\").click( function() { ranking_submit('{$I[0]['file_id']}','{$I[1]['file_id']}', $rank_year) });
            jQuery(\"#ranking_right\").click( function() { ranking_submit('{$I[1]['file_id']}','{$I[0]['file_id']}', $rank_year) });
        </script>
    </div>";

    echo $out;

    if (!$php) {
        wp_die();
    }
}

// update image rank
function unc_ranking_update() {
    global $wpdb;

    $rank_up = filter_input(INPUT_GET, 'rank_up', FILTER_VALIDATE_INT);
    $rank_dn = filter_input(INPUT_GET, 'rank_dn', FILTER_VALIDATE_INT);

    if ($rank_up && $rank_dn) {
        echo "Thanks for rating the image.<br>";
        $rank_table = $wpdb->prefix . "unc_gallery_ranking";
        $sql = "REPLACE INTO `$rank_table` (file_id, ranking, rank_time, ranked)
            VALUES ($rank_up, ranking + 1, now(), ranked + 1),($rank_dn, ranking - 1, now(), ranked + 1);";

        $sql_up = "INSERT INTO `$rank_table` (file_id, ranking, rank_time, ranked) VALUES ($rank_up, 1, now(), 1)
            ON DUPLICATE KEY UPDATE ranking = ranking + 1, ranked = ranked + 1;";
        $wpdb->query($sql_up);

        $sql_dn = "INSERT INTO `$rank_table` (file_id, ranking, rank_time, ranked) VALUES ($rank_dn, 1, now(), 1)
            ON DUPLICATE KEY UPDATE ranking = ranking - 1, ranked = ranked + 1;";
        $wpdb->query($sql_dn);

    } else {

    }
}

/**
 * Select 2 images for ranking
 *
 * @global type $wpdb
 * @return type array
 */
function unc_ranking_select($rank_year = 2020) {
    global $wpdb;

    // first we look for unranked images

    $orientation = unc_ranking_orientation();

    $rank_table = $wpdb->prefix . "unc_gallery_ranking";
    $img_table = $wpdb->prefix . "unc_gallery_img";
    $att_table = $wpdb->prefix . "unc_gallery_att";

    $sql = "SELECT id, att_value, EXTRACT(YEAR FROM file_time) as year
        FROM $img_table
        LEFT JOIN $rank_table ON id=$rank_table.file_id
        LEFT JOIN $att_table on id=$att_table.file_id
        WHERE $rank_table.ranked IS NULL
        AND $att_table.att_name='orientation'
        AND $att_table.att_value='$orientation'
        AND EXTRACT(YEAR FROM file_time) = $rank_year
        ORDER BY RAND() LIMIT 2;";

    // count results
    $unranked = $wpdb->get_results($sql, 'ARRAY_A');

    $max = count($unranked);
    // if we have only 1 images that is not ranked yet, we go to the already ranked ones
    if ($max < 2) {
        //$min_sql = "SELECT ranked, count(file_id) as filecount FROM `$rank_table` GROUP BY ranked ORDER BY ranked ASC;";
        //$rank_data = $wpdb->get_results($min_sql, 'ARRAY_A');
        $i = 1;
        while ($i < 99) {

            // we look now for a group that has at least 2 files ranked, staring with the lowest amount of votes per file
            $sql = "SELECT id, att_value, EXTRACT(YEAR FROM file_time) as year
                FROM $img_table
                LEFT JOIN $rank_table ON id=$rank_table.file_id
                LEFT JOIN $att_table on id=$att_table.file_id
                WHERE $rank_table.ranked=$i
                AND $att_table.att_name='orientation'
                AND $att_table.att_value='$orientation'
                AND  EXTRACT(YEAR FROM file_time) = $rank_year
                ORDER BY RAND() LIMIT 2;";
            $unranked = $wpdb->get_results($sql, 'ARRAY_A');
            if (count($unranked) < 2) {
                $i++;
            } else {
                echo "Round #$i of ranking $orientation of $rank_year images!";
                echo "<!-- " . var_export($sql, true) . " -->";
                $file_1 = $unranked[0]['id'];
                $file_2 = $unranked[1]['id'];
                $data = array(
                    'images' => array($file_1, $file_2),
                    'orientation' => $orientation,
                );
                return $data;
            }
        }
    } else {
        echo "<!-- " . var_export($sql, true) . " -->";

        $file_1 = $unranked[0]['id'];
        $file_2 = $unranked[1]['id'];
        echo "First round of ranking $orientation of $rank_year images!";
        $data = array(
            'images' => array($file_1, $file_2),
            'orientation' => $orientation
        );
        return $data;
    }
}

function ranking_stats() {
    global $wpdb;
    $rank_table = $wpdb->prefix . "unc_gallery_ranking";
    $img_table = $wpdb->prefix . "unc_gallery_img";

    $sql = "SELECT count(id) as counter, EXTRACT(YEAR FROM file_time) as year, ranked
        FROM $img_table
        LEFT JOIN $rank_table ON id=$rank_table.file_id
        GROUP BY year, ranked;";

    $S = $wpdb->get_results($sql, 'ARRAY_A');

    $ranks = array();
    foreach ($S as $s) {
        $year = $s['year'];
        if (!isset($ranks[$year])) {
            $ranks[$year] = array('count' => 0, 'ranks' => array('data'=> array()));
        }
        $ranked = $s['ranked'];
        $count = intval($s['counter']);

        // make a sum of all images per year
        $ranks[$year]['count'] = $ranks[$year]['count'] + $count;
        // collect the ranked data
        $ranks[$year]['ranks']['data'][$ranked] = $count;
    }

    // now we calculate the percentages
    foreach ($ranks as $year => $Y) {
        $ranks_count = max(array_keys($Y['ranks']['data'])) + 1;
        $image_count = $Y['count'];
        $ranks[$year]['percentage'] = $ranks_count / $image_count * 100;
    }

    echo var_export($ranks, true) ;
}

/**
 * We select 2 images with the same orientation
 *
 * @return string
 */
function unc_ranking_orientation() {
    $orientations = array(0 => 'landscape', 1 => 'portrait');

    $orientation = unc_random_number(0, 1);

    $selection = $orientations[$orientation];

    return $selection;
}

function unc_ranking_datepicker($rank_year = false) {
    global $wpdb;

    $img_table = $wpdb->prefix . "unc_gallery_img";

    $sql = "SELECT EXTRACT(YEAR FROM file_time) as year
        FROM $img_table
        GROUP BY year
        ORDER BY year ASC";

    $Y = $wpdb->get_results($sql, 'ARRAY_A');

    $data = array();

    $invalid_years = array(
        2011, 2013
    );

    foreach ($Y as $y) {
        $year = $y['year'];
        if (!in_array($year, $invalid_years)) {
            $data[$year] = $year;
        }
    }

    if (!in_array($rank_year, $data)) {
        $rank_year = false;
    }

    $out = unc_tools_dropdown($data, "rank_year", $rank_year, true, false, false, "Chose Year", '');
    return $out;
}

function unc_ranking_top_show() {






    
}