<?php
    function wplink_statistics() {
    ?>
    <div class="wrap">
        <div id="icon-edit" class="icon32"><br /></div>
        <h2>Statistics</h2>
        <table class="widefat poll fixed" cellspacing="0">
            <thead>	
                <tr>
                    <th scope="col">Name</th>
                    <th scope="col">Total Clicks</th>
                    <th scope="col">Unique Clicks (total)</th>
                    <th scope="col">Total Clicks Today</th>
                    <th scope="col">Unique Clicks Today</th>
                </tr>
            </thead>
            
            <tfoot>
            <tr>
                    <th scope="col">Name</th>
                    <th scope="col">Total Clicks</th>
                    <th scope="col">Unique Clicks (total)</th>
                    <th scope="col">Total Clicks Today</th>
                    <th scope="col">Unique Clicks Today</th>
            </tr>
            </tfoot>

            <tbody id="the-list" class="list:link">
                <?php wplink_stats_rows(); ?>
            </tbody>
        </table>
        
        <h2>Last 100 clicks...</h2>
        <table class="widefat poll fixed" cellspacing="0">
            <thead>	
                <tr>
                    <th scope="col">Link</th>
                    <th scope="col">Time</th>
                    <th scope="col">Destination</th>
                    <th scope="col">Referrer</th>
                    <th scope="col">User Agent</th>
                    <th scope="col">Subid</th>
                </tr>
            </thead>
            
            <tfoot>
            <tr>
                    <th scope="col">Link</th>
                    <th scope="col">Time</th>
                    <th scope="col">Destination</th>
                    <th scope="col">Referrer</th>
                    <th scope="col">User Agent</th>
                    <th scope="col">Subid</th>
            </tr>
            </tfoot>

            <tbody id="the-list" class="list:link">
                <?php wplink_clicks_rows(); ?>
            </tbody>
        </table>    
    </div>
    <?php
    }
    
    
    function wplink_clicks_rows() {
        global $wpdb;

        $query = $wpdb->prepare("SELECT * FROM `".$wpdb->prefix."wplink_clicks` ORDER BY `timestamp` DESC LIMIT 100 ");
        $clicks = $wpdb->get_results($query);
        
        $out = '';
        $count = 0;
        foreach( $clicks as $entry )
                $out .= wplink_clicks_row($entry, ++$count % 2 ? ' class="row-b"' : '' );

        // filter and send to screen
        echo $out;
        return $count;
	}
	
	
	function wplink_clicks_row( $entry, $class = '' ) {
    	global $wpdb;
        
        $link = $wpdb->get_results("SELECT * FROM `".$wpdb->prefix."wplink_links` WHERE `id`='{$entry->link_id}'");

        $name = $link[0]->name;
        if(!(strlen($name) > 0)) {
            $name = $link[0]->link_title;
        }
        
        $output = "<tr id='entry-" . $entry->id . "'>";
        $output .= "<td class='name column-name'><strong>" . $name . "</strong></td>";
        $output .= "<td class='timestamp column-from'>" . date("F j, Y, g:i a", $entry->timestamp) . "</td>";
        $output .= "<td class='destination column-to'>" . ($entry->destination)  . "</td>";
        $output .= "<td class='referrer column-cloaked'>" .(!empty($entry->referrer) ? $entry->referrer : "n/a") . "</td>";
        $output .= "<td class='agent column-group'><small>" . ($entry->agent). "</small></td>";
        $output .= "<td class='subid column-group'>" . ($entry->subid). "</td>";
        
        $output .= "</tr>";
        
        return $output;
        
	}
	
    function wplink_stats_rows() {
        global $wpdb;

        $query = $wpdb->prepare("SELECT * FROM `".$wpdb->prefix."wplink_links");
        $clicks = $wpdb->get_results($query);
        
        $out = '';
        $count = 0;
        foreach( $clicks as $entry )
                $out .= wplink_stats_row($entry, ++$count % 2 ? ' class="row-b"' : '' );

        // filter and send to screen
        echo $out;
        return $count;
	}
	
	
	function wplink_stats_row( $entry, $class = '' ) {
    	global $wpdb;
                
        $name = $entry->name;
        if(!(strlen($name) > 0)) {
            $name = $entry->link_title;
        }
        
        $today = strtotime("today");
        
        $total_clicks = $wpdb->get_results("SELECT COUNT(*) as `cn` FROM `".$wpdb->prefix."wplink_clicks` WHERE `link_id`='{$entry->id}'");
        $unique_total_clicks = $wpdb->get_results("SELECT COUNT(DISTINCT `ip_long`) as `cn` FROM `".$wpdb->prefix."wplink_clicks` WHERE `link_id`='{$entry->id}'");
        $daily_total_clicks = $wpdb->get_results("SELECT COUNT(*) as `cn` FROM `".$wpdb->prefix."wplink_clicks` WHERE `link_id`='{$entry->id}' AND `timestamp` < NOW() AND `timestamp` AND `timestamp` > $today");
        $unique_daily_total_clicks = $wpdb->get_results("SELECT COUNT(DISTINCT `ip_long`) as `cn` FROM `".$wpdb->prefix."wplink_clicks` WHERE `link_id`='{$entry->id}' AND `timestamp` < NOW() AND `timestamp` AND `timestamp` > $today");
        
        $output = "<tr id='entry-" . $entry->id . "'>";
        $output .= "<td class='name column-name'><strong>" . $name . "</strong> (" . $entry->id . ")</td>";
        $output .= "<td class='from column-from'>" . (is_numeric($total_clicks[0]->cn) ? $total_clicks[0]->cn : 0) . "</td>";
        $output .= "<td class='to column-to'>" . (is_numeric($unique_total_clicks[0]->cn) ? $unique_total_clicks[0]->cn : 0)  . "</td>";
        $output .= "<td class='cloaked column-cloaked'>" .(is_numeric($daily_total_clicks[0]->cn) ? $daily_total_clicks[0]->cn : 0) . "</td>";
        $output .= "<td class='group column-group'>" . (is_numeric($unique_daily_total_clicks[0]->cn) ? $unique_daily_total_clicks[0]->cn : 0). "</td>";
        $output .= "</tr>";
        
        if($entry->subid == 1) {
            $unique_subid = $wpdb->get_results("SELECT DISTINCT `subid` FROM `".$wpdb->prefix . "wplink_clicks` WHERE `link_id`='{$entry->id}'");
            foreach($unique_subid as $subid) {
                if($subid->subid != "") {
                    $output .= "<tr><td>&nbsp; <small><strong>Subid: " . $subid->subid . "</strong></small></td>";
                        
                    $total_clicks = $wpdb->get_results("SELECT COUNT(*) as `cn` FROM `".$wpdb->prefix."wplink_clicks` WHERE `link_id`='{$entry->id}' AND `subid`='{$subid->subid}'");
                    $unique_total_clicks = $wpdb->get_results("SELECT COUNT(DISTINCT `ip_long`) as `cn` FROM `".$wpdb->prefix."wplink_clicks` WHERE `link_id`='{$entry->id}' AND `subid`='{$subid->subid}'");
                    $daily_total_clicks = $wpdb->get_results("SELECT COUNT(*) as `cn` FROM `".$wpdb->prefix."wplink_clicks` WHERE `link_id`='{$entry->id}' AND `timestamp` < NOW() AND `timestamp` AND `timestamp` > $today AND `subid`='{$subid->subid}'");
                    $unique_daily_total_clicks = $wpdb->get_results("SELECT COUNT(DISTINCT `ip_long`) as `cn` FROM `".$wpdb->prefix."wplink_clicks` WHERE `link_id`='{$entry->id}' AND `timestamp` < NOW() AND `timestamp` AND `timestamp` > $today AND `subid`='{$subid->subid}'");
        
                    $output .= "<td class='from column-from'><small>" . (is_numeric($total_clicks[0]->cn) ? $total_clicks[0]->cn : 0) . "</small></td>";
                    $output .= "<td class='to column-to'><small>" . (is_numeric($unique_total_clicks[0]->cn) ? $unique_total_clicks[0]->cn : 0)  . "</small></td>";
                    $output .= "<td class='cloaked column-cloaked'><small>" .(is_numeric($daily_total_clicks[0]->cn) ? $daily_total_clicks[0]->cn : 0) . "</small></td>";
                    $output .= "<td class='group column-group'><small>" . (is_numeric($unique_daily_total_clicks[0]->cn) ? $unique_daily_total_clicks[0]->cn : 0). "</small></td>";
                    $output .= "</tr>";
                }                               
            }
        }
        
        return $output;
        
	}
	
?>