<?php 

// $Id$



/**

 * @file

 * Hnadles most of the stuff related to Game page.

 */



function propicks_pick_exists($analyst, $game) {  //$analyst = uid, $game = nid

  return db_result(db_query("SELECT n.nid FROM {content_type_pick} n WHERE field_analyst_uid = %d AND field_game_nid = %d", $analyst, $game));

}



/*

 * return an aggregate line pick percentage for a game

 */



function propicks_get_game_all_line_picks_percentage($nid, $reset = FALSE) {

  static $game_all_line_picks_percentage = array();

  if ($reset) {

    unset($game_all_line_picks_percentage);

    cache_clear_all('propicks', 'cache_propicks' , TRUE);

  }

  $do_calculations = TRUE;

  if (isset($game_all_line_picks_percentage[$nid])) { // if data is in var, don't do calculations

    $do_calculations = FALSE;

  }

  else { // if data is in cache, don't do calculations

    $cache = cache_get('propicks_game_all_line_picks_percentage', 'cache_propicks');

    $game_all_line_picks_percentage = unserialize($cache->data);

    if (isset($game_all_line_picks_percentage[$nid])) { 

      $do_calculations = FALSE;  

    }

  }

  

  //$do_calculations = TRUE;

  if ($do_calculations) {

    // go over all picks, select ones pertaining to the game, average them Win = 1, Loss = 2.

    $sql = "SELECT p.field_linepick_value

      FROM {content_type_pick} p 

      WHERE field_game_nid = %d

      AND field_line_value IS NOT NULL";

    $result = db_query($sql, $nid);

    $num_rows = 0;

    $total = 0;

    while($data = db_fetch_object($result)) {

      if ($data->field_linepick_value == 1 || $data->field_linepick_value == 2) {

        $num_rows += 1;

      }    

      if ($data->field_linepick_value == 2) {

        $total += 1;

      }

    }

    if ($num_rows == 0) {

      $game_all_line_picks_percentage[$nid] = array('percentage' => '-1', 'num_picks' => '0', 'team2' => '0');

    }

    else {

      $game_all_line_picks_percentage[$nid] = array('percentage' => round(100*$total/$num_rows), 'num_picks' => $num_rows, 'team2' => $total);

    }

    cache_set('propicks_game_all_line_picks_percentage', 'cache_propicks', serialize($game_all_line_picks_percentage));    

  }

  return $game_all_line_picks_percentage[$nid];

}



/*

 * get all straight picks percentage

 */

 

function propicks_get_game_straight_picks_percentage($nid, $reset = FALSE) {

  static $game_straight_picks_percentage = array();

  

  if ($reset) {

    unset($game_straight_picks_percentage);

    cache_clear_all('propicks_game_straight_picks_percentage', 'cache_propicks' , FALSE);

  }

  $do_calculations = TRUE;

  if (isset($game_straight_picks_percentage[$nid])) { // if data is in var, don't do calculations

    $do_calculations = FALSE;

  }

  else { // if data is in cache, don't do calculations

    $cache = cache_get('propicks_game_straight_picks_percentage', 'cache_propicks');

    $game_straight_picks_percentage = unserialize($cache->data);

    if (isset($game_straight_picks_percentage[$nid])) { 

      $do_calculations = FALSE;  

    }

  }

  

  //$do_calculations = TRUE;

  

  if ($do_calculations) {

    $sql = "SELECT p.field_win_loss_value

      FROM {content_type_pick} p 

      WHERE field_game_nid = %d

      AND field_win_loss_value IS NOT NULL

      AND field_win_loss_value != 0 

      ";

    $result = db_query($sql, $nid);

    $num_rows = 0;

    $total = 0;

    while($data = db_fetch_object($result)) {

      if ($data->field_win_loss_value == 1 || $data->field_win_loss_value == 2) { 

        $num_rows += 1;      

      }



      if ($data->field_win_loss_value == 2) {

        $total += 1;

      }

    }

    

    if ($num_rows == 0) {

      $game_straight_picks_percentage[$nid] = array('percentage' => '-1', 'num_picks' => '0');

    }

    else {

      $game_straight_picks_percentage[$nid] = array('percentage' => round(100*$total/$num_rows), 'num_picks' => $num_rows);

    }

    cache_set('propicks_game_straight_picks_percentage', 'cache_propicks', serialize($game_straight_picks_percentage));

  }

  

  return $game_straight_picks_percentage[$nid];

}



/*

 * get an average line for a game

 */



function propicks_get_game_average_line($nid, $reset = FALSE) {

  static $games_average_line = array();

    if ($reset) {

      unset($games_average_line);

      cache_clear_all('propicks', 'cache_propicks' , TRUE);

    }

    $do_calculations = TRUE;

    if (isset($games_average_line[$nid])) { // if data is in var, don't do calculations

      $do_calculations = FALSE;

    }

    else { // if data is in cache, don't do calculations

      $cache = cache_get('propicks_games_average_line', 'cache_propicks');

      $games_average_line = unserialize($cache->data);

      if (isset($games_average_line[$nid])) { 

        $do_calculations = FALSE;  

      }

    }

  

  if ($do_calculations) {

    $sql = "SELECT p.field_line_value

      FROM {content_type_pick} p 

      WHERE field_game_nid = %d

      AND field_line_value IS NOT NULL 

      GROUP BY field_line_value";

    $result = db_query($sql, $nid);

    $lines = array();

    while($data = db_fetch_object($result)) {

      $lines[] = $data->field_line_value;

    }

    if (count($lines) != 0) {

      $average = array_sum($lines)/count($lines);

      $games_average_line[$nid] = round($average*2)/2;    

    }

    else {

      $games_average_line[$nid] = NULL;        

    }



    

    cache_set('propicks_games_average_line', 'cache_propicks', serialize($games_average_line));

  }

  

  return $games_average_line[$nid];



}





/* 

 * return a number with + or - sign in front of it

 */

function propicks_number_with_sign($number, $picked_team = NULL) {

  if ($picked_team == NULL || $picked_team == '2') {

    if ($number > 0) {

      return '+' . $number;

    }

    else {

      return $number;

    }

  }

  elseif ($picked_team == '1') {

    return propicks_number_with_sign(-$number);

  }

}



/*

 * returns a list of picks for a game and the playing team

*/

function propicks_get_game_picks($gid, $team = 1) {

  $sql = "SELECT p.field_win_loss_value, p.field_analyst_uid, p.field_line_value

    FROM {content_type_pick} p 

    WHERE field_game_nid = %d

    AND field_win_loss_value = %d";

  $result = db_query($sql, $gid, $team);

  $picks = array();

  while($data = db_fetch_object($result)) {

    $analyst = user_load(array('uid' => $data->field_analyst_uid));

    $picks['pick'][] = $data ;

    $pick['analyst'][] = array('first_name' => $analyst->profile_first_name, 'last_name' => $analyst->profile_last_name, 'organization' => $analyst->profile_organization);

  }

  return $picks;

}



function propicks_get_game_comments($nid, $reset = FALSE) {

  static $game_comments = array();

  if ($reset) {

    unset($game_comments );

    cache_clear_all('propicks', 'cache_propicks' , TRUE);

  }



  // determine whether we should do calculations 

  $do_calculations = TRUE;

  if (isset($game_comments[$nid])) { // if data is in var, don't do calculations

    $do_calculations = FALSE;

  }

  else { // if data is in cache, don't do calculations

    $cache = cache_get('propicks_game_comments', 'cache_propicks');

    $game_comments = unserialize($cache->data);

    if (isset($game_comments[$nid])) { 

      $do_calculations = FALSE;  

    }

  }

  

  if ($do_calculations) {

    $sql = "SELECT p.field_comment_value, p.field_analyst_uid, p.field_win_loss_value, p.field_line_value, p.field_linepick_value

    FROM {content_type_pick} p

    WHERE field_game_nid = %d

    ";

    $result = db_query($sql, $nid);

    $comments = array();

    while($data = db_fetch_object($result)) {

      if ($data->field_comment_value) {

        $comments[] = $data;

      }

    }

    $game_comments[$nid] = $comments;

    cache_set('propicks_game_comments', 'cache_propicks', serialize($game_comments));

  }

  return $game_comments[$nid];

}



function propicks_get_team_name_by_pick($pick_win_loss, $game, $reset = FALSE) {

  static $team_name = array();

  if ($reset) {

    unset($team_name);

  }

  $do_calculations = TRUE;

  if ($pick_win_loss == '1') {

    $nid = $game->field_team_1['0']['nid'];

  }

  elseif ($pick_win_loss == '2') {

    $nid = $game->field_team_2['0']['nid'];  

  }

  

  if (isset($team_name[$nid])) { // if data is in var, don't do calculations

    $do_calculations = FALSE;

  }  



  if ($do_calculations) {

    $team_name[$nid] = db_result(db_query("SELECT n.title

      FROM {node} n 

      WHERE nid = %d

      ", $nid));

  }

  

  return $team_name[$nid];

}



function propicks_get_analysts_all_picks($uid, $reset = FALSE) {

  static $analysts_all_picks = array();

  if ($reset) {

    unset($analysts_all_picks);

    cache_clear_all('propicks', 'cache_propicks' , TRUE);

  }



  // determine whether we should do calculations 

  $do_calculations = TRUE;

  if (isset($analysts_all_picks[$uid])) { // if data is in var, don't do calculations

    $do_calculations = FALSE;

  }

  else { // if data is in cache, don't do calculations

    $cache = cache_get('propicks_analysts_all_picks', 'cache_propicks');

    $analysts_all_picks = unserialize($cache->data);

    if (isset($analysts_all_picks[$uid])) { 

      $do_calculations = FALSE;  

    }

  }

  //$do_calculations = TRUE;  

  if ($do_calculations) {

    $picks = array();

    $sql = "SELECT * FROM {content_type_pick} p 

      WHERE field_analyst_uid = %d

      ORDER BY field_game_nid DESC";  

    $result = db_query($sql, $uid);

    $team = array();

    while($data = db_fetch_object($result)) {

      $game = node_load($data->field_game_nid);

      $team['1'] = node_load($game->field_team_1['0']['nid']);

      $team['2'] = node_load($game->field_team_2['0']['nid']);

      

      if ($data->field_win_loss_value == '1') {

        $picked_team = $team['1']->title;

      }

      elseif ($data->field_win_loss_value == '2') {

        $picked_team = $team['2']->title;    

      }

      else {

      $picked_team = 'N/A';    

      }

      

      if ($data->field_linepick_value == '1') {

        $picked_team_vs_spread = $team['1']->title;

      }

      elseif ($data->field_linepick_value == '2') {

        $picked_team_vs_spread = $team['2']->title;    

      }

      elseif ($data->field_linepick_value == '3') {

        $picked_team_vs_spread = t('TIE');    

      }      

      else {

      $picked_team_vs_spread = 'N/A';    

      }

      

      $picks[] = array(

        'pick_id' => $data->nid,

        'date' => date("M j Y, g:i", $game->field_datestamp_0['0']['value']),

        'game' => l($team['1']->title . ' @ ' . $team['2']->title, "node/" . $data->field_game_nid, NULL,NULL,NULL,NULL,FALSE),

        'score' => $game->field_score1['0']['value'] . ' - ' . $game->field_score2['0']['value'] ,

        'line' => $data->field_line_value,

        'picked_team' => $picked_team,

        'picked_team_vs_spread' => $picked_team_vs_spread,

      );

    }

    $analysts_all_picks[$uid] = $picks;  

    cache_set('propicks_analysts_all_picks', 'cache_propicks', serialize($analysts_all_picks));    

  }



  return $analysts_all_picks[$uid];

}



function propicks_get_analyst_accuracy_straight($uid, $date = NULL, $reset = FALSE) {

  static $users_accuracy;

  if ($reset) {

    unset($users_accuracy);

    cache_clear_all('propicks', 'cache_propicks' , TRUE);

  }



  if ($date == NULL) {

    $date_index = date("Y-m-d", strtotime('+2 days', strtotime("+1 day" . "last Tuesday", time())));

    $date_unix = time();

  }

  else {

    $date_index = date("Y-m-d", strtotime('+2 days', strtotime("+1 day" . "last Tuesday", $date)));        

    $date_unix = $date;

  }



  // determine whether we should do calculations 

  $do_calculations = TRUE;

  if (isset($users_accuracy[$uid][$date_index])) { // if data is in var, don't do calculations

    $do_calculations = FALSE;

  }

  else { // if data is in cache, don't do calculations

    $cache = cache_get('propicks_users_accuracy', 'cache_propicks');

    $users_accuracy = unserialize($cache->data);  

    if (isset($users_accuracy[$uid][$date_index])) { 

      $do_calculations = FALSE;  

    }

  }

  

  if ($do_calculations) {

    $num_counted_picks = 0;

    $correct = 0;

    //$sql = "SELECT p.nid FROM {content_type_pick} p WHERE p.field_analyst_uid = %d AND field_win_loss_value IS NOT NULL";

    $sql = "SELECT p.nid, g.field_datestamp_0_value FROM {content_type_pick} p LEFT JOIN {content_type_game} g ON p.field_game_nid = g.nid WHERE p.field_analyst_uid = %d AND field_win_loss_value IS NOT NULL";

    $result = db_query($sql, $uid);

    while($data = db_fetch_object($result)) {

      if ($data->field_datestamp_0_value <= $date_unix) {

        $num_counted_picks += 1;

        $pick_correct = propicks_is_pick_correct($data->nid);

        if ($pick_correct == 1) {

          $correct += 1;

        }

        if ($pick_correct == -1) {

          $num_counted_picks -= 1;

        }

      }

    }

    

    if ($num_counted_picks == '0') {

      $percentage = '0';    

    }

    else {

      $percentage = round(100*$correct/$num_counted_picks);      

    }

    

    $users_accuracy[$uid][$date_index] = array(

      'num_counted_picks' => $num_counted_picks,

      'percentage' => $percentage, 

      'correct' => $correct,

      'incorrect' => $num_counted_picks - $correct,

      );

    cache_set('propicks_users_accuracy', 'cache_propicks', serialize($users_accuracy));        

  }

  return $users_accuracy[$uid][$date_index];

}





function propicks_get_analyst_accuracy_vs_spread($uid, $date = NULL, $reset = FALSE) {

  static $users_accuracy_vs_spread;

  if ($reset) {

    unset($users_accuracy_vs_spread);

    cache_clear_all('propicks', 'cache_propicks' , TRUE);

  }

  if ($date == NULL) {

    $date_index = date("Y-m-d", strtotime('+2 days', strtotime("+1 day" . "last Tuesday", time())));

    $date_unix = time();

  }

  else {

    $date_index = date("Y-m-d", strtotime('+2 days', strtotime("+1 day" . "last Tuesday", $date)));        

    $date_unix = $date;

  }

  

  // determine whether we should do calculations 

  $do_calculations = TRUE;

  if (isset($users_accuracy_vs_spread[$uid][$date_index])) { // if data is in var, don't do calculations

    $do_calculations = FALSE;

  }

  else { // if data is in cache, don't do calculations

    $cache = cache_get('propicks_users_accuracy_vs_spread', 'cache_propicks');

    $users_accuracy_vs_spread = unserialize($cache->data);  

    if (isset($users_accuracy_vs_spread[$uid][$date_index])) { 

      $do_calculations = FALSE;  

    }

  }

  

  if ($do_calculations) { 

    $num_counted_picks = 0;

    $correct = 0;  

    //$sql = "SELECT p.nid FROM {content_type_pick} p WHERE p.field_analyst_uid = %d AND field_linepick_value IS NOT NULL";

    $sql = "SELECT p.nid, g.field_datestamp_0_value FROM {content_type_pick} p LEFT JOIN {content_type_game} g ON p.field_game_nid = g.nid WHERE p.field_analyst_uid = %d AND field_linepick_value IS NOT NULL";    



    $result = db_query($sql, $uid);

    while($data = db_fetch_object($result)) {

      if ($data->field_datestamp_0_value <= $date_unix) {

        $num_counted_picks += 1;

        $pick_correct = propicks_is_pick_correct_vs_spread($data->nid);

        if ($pick_correct == 1) {

          $correct += 1;

        }

        if ($pick_correct == -1) {

          $num_counted_picks -= 1;

        }

      }

    }

    if ($num_counted_picks == '0') {

      $percentage = '0';    

    }

    else {

      $percentage = round(100*$correct/$num_counted_picks);      

    }    

    $users_accuracy_vs_spread[$uid][$date_index] = array(

      'num_counted_picks' => $num_counted_picks,

      'percentage' => $percentage,

      'correct' => $correct,

      'incorrect' => $num_counted_picks - $correct,

      );

    cache_set('propicks_users_accuracy_vs_spread', 'cache_propicks', serialize($users_accuracy_vs_spread));        

  }

  

  return $users_accuracy_vs_spread[$uid][$date_index];

}



/**

 * Determine whether a given pick is correct

 * returns

 * +1 if correct

 * 0 if incorrect

 * -1 if not available

 */



function propicks_is_pick_correct($pick_id, $reset = FALSE) {

  static $pick_accuracy = array();

  if ($reset) {

    unset($pick_accuracy);

    cache_clear_all('propicks', 'cache_propicks' , TRUE);

  }

  

  // determine whether we should do calculations 

  $do_calculations = TRUE;

  if (isset($pick_accuracy[$pick_id])) { // if data is in var, don't do calculations

    $do_calculations = FALSE;

  }

  

  else { // if data is in cache, don't do calculations

    $cache = cache_get('propicks_pick_accuracy', 'cache_propicks');

    $pick_accuracy = unserialize($cache->data);  

    if (isset($pick_accuracy[$pick_id])) { 

      $do_calculations = FALSE;  

    }

  }



  if ($do_calculations) {

    

    $data = db_fetch_object(db_query("SELECT p.field_game_nid, p.field_win_loss_value, g.field_score1_value, g.field_score2_value FROM {content_type_pick} p LEFT JOIN {content_type_game} g ON p.field_game_nid = g.nid WHERE p.nid = %d", $pick_id));    



    if (!$data->field_win_loss_value) {

      $pick_accuracy[$pick_id] = '-1';

      cache_set('propicks_pick_accuracy', 'cache_propicks', serialize($pick_accuracy));

      return $pick_accuracy[$pick_id];

    }

    

    if ($data->field_score1_value == NULL || $data->field_score2_value == NULL) {

      $pick_accuracy[$pick_id] = '-1';

      cache_set('propicks_pick_accuracy', 'cache_propicks', serialize($pick_accuracy));

      return $pick_accuracy[$pick_id];

    }    

    

    if ($data->field_win_loss_value == 2) {

      if ($data->field_score2_value - $data->field_score1_value > 0 ) {

        $pick_accuracy[$pick_id] = '1';

        cache_set('propicks_pick_accuracy', 'cache_propicks', serialize($pick_accuracy));

        return $pick_accuracy[$pick_id];

      }

    }

    if ($data->field_win_loss_value == 1) {

      if ($data->field_score1_value - $data->field_score2_value > 0 ) {

        $pick_accuracy[$pick_id] = '1';

        cache_set('propicks_pick_accuracy', 'cache_propicks', serialize($pick_accuracy));

        return $pick_accuracy[$pick_id];

      }

    }

    

    $pick_accuracy[$pick_id] = '0';

    cache_set('propicks_pick_accuracy', 'cache_propicks', serialize($pick_accuracy));

    return $pick_accuracy[$pick_id];    

  }

  else {

    return $pick_accuracy[$pick_id];

  }

}







function propicks_is_pick_correct_vs_spread($pick_id, $reset = FALSE) {

  static $pick_accuracy_vs_spread = array();

  if ($reset) {

    unset($pick_accuracy_vs_spread);

    cache_clear_all('propicks', 'cache_propicks' , TRUE);

  }

  

  // determine whether we should do calculations 

  $do_calculations = TRUE;

  if (isset($pick_accuracy_vs_spread[$pick_id])) { // if data is in var, don't do calculations

    $do_calculations = FALSE;

  }

  else { // if data is in cache, don't do calculations

    $cache = cache_get('propicks_pick_accuracy_vs_spread', 'cache_propicks');

    $pick_accuracy_vs_spread = unserialize($cache->data);  

    if (isset($pick_accuracy_vs_spread[$pick_id])) { 

      $do_calculations = FALSE;  

    }

  }  



  if ($do_calculations) { 

    $data = db_fetch_object(db_query("SELECT p.field_game_nid, p.field_line_value, p.field_linepick_value, g.field_score1_value, g.field_score2_value FROM {content_type_pick} p LEFT JOIN {content_type_game} g ON p.field_game_nid = g.nid WHERE p.nid = %d", $pick_id));    

  

    //$pick = node_load($pick_id);

    //$game = node_load($pick->field_game['0']['nid']);



    if (!$data->field_linepick_value) {

      $pick_accuracy_vs_spread[$pick_id] = '-1';

      cache_set('propicks_pick_accuracy_vs_spread', 'cache_propicks', serialize($pick_accuracy_vs_spread));

      return $pick_accuracy_vs_spread[$pick_id];

    }

    

    if ($data->field_score1_value == NULL || $data->field_score2_value == NULL) {

      $pick_accuracy_vs_spread[$pick_id] = '-1';

      cache_set('propicks_pick_accuracy_vs_spread', 'cache_propicks', serialize($pick_accuracy_vs_spread));

      return $pick_accuracy_vs_spread[$pick_id];

    }

    

    if ($data->field_linepick_value == 2) {

      if ($data->field_score2_value - $data->field_score1_value + $data->field_line_value > 0 ) {

        $pick_accuracy_vs_spread[$pick_id] = '1';

        cache_set('propicks_pick_accuracy_vs_spread', 'cache_propicks', serialize($pick_accuracy_vs_spread));

        return $pick_accuracy_vs_spread[$pick_id];

      }

    }

    if ($data->field_linepick_value == 1) {

      if ($data->field_score1_value - $data->field_score2_value - $data->field_line_value > 0 ) {

        $pick_accuracy_vs_spread[$pick_id] = '1';

        cache_set('propicks_pick_accuracy_vs_spread', 'cache_propicks', serialize($pick_accuracy_vs_spread));

        return $pick_accuracy_vs_spread[$pick_id];

      }

    }

    

    if (( 

      (

        $data->field_linepick_value == 2 && 

        $data->field_score2_value- $data->field_score1_value + $data->field_line_value == 0 

      )

      || 

      (

        $data->field_linepick_value == 1 && 

        $data->field_score1_value- $data->field_score2_value - $data->field_line_value == 0         

      )

    ))

      {

        $pick_accuracy_vs_spread[$pick_id] = '-1';

        cache_set('propicks_pick_accuracy_vs_spread', 'cache_propicks', serialize($pick_accuracy_vs_spread));

        return $pick_accuracy_vs_spread[$pick_id];

      }

    $pick_accuracy_vs_spread[$pick_id] = '0';

    cache_set('propicks_pick_accuracy_vs_spread', 'cache_propicks', serialize($pick_accuracy_vs_spread));

    return $pick_accuracy_vs_spread[$pick_id];    

  }

  else {

    return $pick_accuracy_vs_spread[$pick_id];

  }

}





function propicks_clear_cache_all() {

   cache_clear_all('propicks', 'cache_propicks' , TRUE);

   drupal_set_message(t('All cache for module propicks is cleared'));

   print theme("page", $output);

}



function _propicks_array_remove_value($val, &$arr){

  $i=array_search($val,$arr);

  if($i===false)return false;

  $arr=array_merge(array_slice($arr, 0,$i), array_slice($arr, $i+1));

  return true;

}



/*

 * returns all percentages by line

 */



function propicks_get_pick_percentage_by_line($gid, $sort = 'lines') {



  //calculate aggregate picks for now, later, all aggregates will be retrived from saved variables later 

  // go over all picks, select ones pertaining to the game, average them Win = 1, Loss = 2.

  $sql = "SELECT p.field_linepick_value, p.field_line_value

    FROM {content_type_pick} p 

    WHERE field_game_nid = %d

    AND field_linepick_value IS NOT NULL

    AND field_linepick_value != '0'

    AND field_line_value IS NOT NULL

    ";

  $result = db_query($sql, $gid);

  $num_picks = array();

  $total = array();

  while($data = db_fetch_object($result)) {

    if (!array_key_exists($data->field_line_value, $num_picks)) {

      $num_picks[$data->field_line_value] = 1;

      if ($data->field_linepick_value == 2) {

        $total[$data->field_line_value] = 1;

      }

      else {

        $total[$data->field_line_value] = 0;      

      }

    }

    else {

      $num_picks[$data->field_line_value] = $num_picks[$data->field_line_value] + 1;

      if ($data->field_linepick_value == 2) {

        $total[$data->field_line_value] = $total[$data->field_line_value] + 1;

      }

    }

  }



  if ($sort == 'num_picks') {

    asort($num_picks);

  }

  if ($sort == 'lines') {

    ksort($num_picks);

  }



  $rows = array();

  foreach ($num_picks as $key => $row) {

    $rows[] = array('line' => $key, 'for_home_team' => $total[$key], 'total_picks' => $num_picks[$key]);

  }

  

  return $rows;

  

}



function propicks_get_top_analysts($date = NULL, $pick_type = 'straight', $num = 10) {

  $sql = "SELECT DISTINCT p.field_analyst_uid

    FROM {content_type_pick} p";

  $result = db_query($sql);

  $analysts = array();

  while($data = db_fetch_object($result)) {

    if ($pick_type == 'straight') {

      $accuracy = propicks_get_analyst_accuracy_straight($data->field_analyst_uid, $date);    

    }

    else {

      $accuracy = propicks_get_analyst_accuracy_vs_spread($data->field_analyst_uid, $date);    

    }

    if ($accuracy['num_counted_picks'] > 30) {

    $analysts[$data->field_analyst_uid] = array(

      'accuracy' => $accuracy['percentage'], 

      'num_picks' => $accuracy['num_counted_picks'], 

      'correct' => $accuracy['correct'], 

      'incorrect' => $accuracy['incorrect'], 

      'uid' => $data->field_analyst_uid

    );    

    }

   }



  asort($analysts);

  $a = array();

  for ( $i = 0; $i < $num; $i += 1) {

    $a[] = array_pop($analysts);

  }  

  return $a;

}



function propicks_get_analyst_game_straight_pick($analyst_uid, $game_nid) {

  return db_result(db_query("SELECT p.field_win_loss_value

    FROM {content_type_pick} p 

    WHERE field_analyst_uid = %d

    AND field_game_nid = %d

    AND field_win_loss_value IS NOT NULL

    AND field_win_loss_value != 0

    ", $analyst_uid, $game_nid));

}



function propicks_get_analyst_game_line_pick($analyst_uid, $game_nid) {

  return db_fetch_object(db_query("SELECT p.field_linepick_value, p.field_line_value

    FROM {content_type_pick} p 

    WHERE field_analyst_uid = %d

    AND field_game_nid = %d

    AND field_linepick_value IS NOT NULL

    AND field_linepick_value != 0

    ", $analyst_uid, $game_nid));

}



function propicks_game_all_picks_page() {

  $output = '';



  // check arg to be a game ID, if not, return not found

  if (!is_numeric(arg(1))) {

    return drupal_not_found();  

  }

  $game = node_load(arg(1));

  $team1 = node_load($game->field_team_1['0']['nid']); 

  $team2 = node_load($game->field_team_2['0']['nid']);   

  if ($game->type != 'game') {

    return drupal_not_found();    

  }

  if ($game->field_score1['0']['value'] != NULL && $game->field_score2['0']['value'] != NULL) {

    $score1 = $game->field_score1['0']['value'];

    $score2 = $game->field_score2['0']['value'];

  }

  else {

    $score1 = NULL;

    $score2 = NULL;

  }



  $overall_percentage_straight = propicks_get_game_straight_picks_percentage($game->nid);

  $overall_percentage_line = propicks_get_game_all_line_picks_percentage($game->nid);  

  $output .= '<div class = "h2">' . $team1->title . ' at ' . $team2->title . '</div>';

  $output .= '<div class = "h3">' . 'Score: ' . $team1->field_abbr['0']['value'] . ' ' . $score1 . ' - ' . $team2->field_abbr['0']['value'] . ' ' . $score2 .  '</div>';

  $output .= '<div class = "h3">' .

    t('Straight picks percentage for the game: ') . 

    $team1->field_abbr['0']['value'] . ' ' . (100 - $overall_percentage_straight['percentage']) . '% - ' . 

    $team2->field_abbr['0']['value'] . ' ' . $overall_percentage_straight['percentage'] . '%' . 

    '<br>' . 

    t('Line picks percentage for the game: ') .     

    $team1->field_abbr['0']['value'] . ' ' . (100 - $overall_percentage_line['percentage']) . '% - ' . 

    $team2->field_abbr['0']['value'] . ' ' . $overall_percentage_line['percentage'] . '%'

    

    ;

  $output .= '</div>';

  

  $header = array( 

    "Record<br>straight", 

    "Record<br>vs spread", 

    "Analyst", 

    "Organization", 

    t('Straight pick') . '<br>' . substr($team1->title, strrpos($team1->title, ' ')), 

    t('Straight pick') . '<br>' . substr($team2->title, strrpos($team2->title, ' ')), 

    "Line for<br/>" . substr($team2->title, strrpos($team2->title, ' ')), 

    t('Line pick') . '<br>' . substr($team1->title, strrpos($team1->title, ' ')), 

    t('Line pick') . '<br>' . substr($team2->title, strrpos($team2->title, ' ')),   

  );



  //get all picks for $game->nid

  $rows = array();



    

  $sql = "SELECT p.nid, p.field_analyst_uid, p.field_line_value, p.field_win_loss_value, p.field_linepick_value FROM {content_type_pick} p 

    WHERE field_game_nid = %d

    ORDER BY field_game_nid DESC";  

  $result = db_query($sql, $game->nid);

  while($data = db_fetch_object($result)) {

    $row = array();

    $analyst = user_load(array('uid' => $data->field_analyst_uid));    

      

    $analyst_accuracy = propicks_get_analyst_accuracy_straight($data->field_analyst_uid, pc_get_last_thursday($game->field_datestamp_0['0']['value']));

    $analyst_accuracy_vs_spread = propicks_get_analyst_accuracy_vs_spread($data->field_analyst_uid, pc_get_last_thursday($game->field_datestamp_0['0']['value']));

    if ($analyst_accuracy['percentage'] == '0') {

      $analyst_accuracy['percentage'] = ' - ';

    }

    else {

      $analyst_accuracy['percentage'] = $analyst_accuracy['percentage']  . '%' . ' (' . $analyst_accuracy['correct'] . '-' . $analyst_accuracy['incorrect'] . ')';

    }

    if ($analyst_accuracy_vs_spread['percentage'] == '0') {

      $analyst_accuracy_vs_spread['percentage'] = ' - ';

    }

    else {

      $analyst_accuracy_vs_spread['percentage'] = $analyst_accuracy_vs_spread['percentage']  . '%' . ' (' . $analyst_accuracy_vs_spread['correct'] . '-' . $analyst_accuracy_vs_spread['incorrect'] . ')';

    }

    global $base_path;

    $directory = path_to_theme();

    if ($data->field_win_loss_value == '1') {

      $visitor_team_tick = '<img src = "' . $base_path . $directory . '/images/tick.gif" style="float: top">';

      $home_team_tick = NULL;

    }

    elseif ($data->field_win_loss_value == '2') {

      $visitor_team_tick = NULL;

      $home_team_tick = '<img src = "' . $base_path . $directory . '/images/tick.gif" style="float: top">';

    }

    else {

      $visitor_team_tick = NULL;

      $home_team_tick = NULL;          

    }    



    if ($data->field_linepick_value == '1') {

      $visitor_team_tick_line = '<img src = "' . $base_path . $directory . '/images/tick.gif" style="float: top">';

      $home_team_tick_line = NULL;



    }

    elseif ($data->field_linepick_value == '2') {

      $visitor_team_tick_line = NULL;

      $home_team_tick_line = '<img src = "' . $base_path . $directory . '/images/tick.gif" style="float: top">';

    }

    else {

      $visitor_team_tick_line = NULL;

      $home_team_tick_line = NULL;          

    }        

    

    $row[] = array('data' => $analyst_accuracy['percentage'], 'align' => 'center');

    $row[] = array('data' => $analyst_accuracy_vs_spread['percentage'], 'align' => 'center');

    $row[] = array('data' => l($analyst->profile_first_name . ' ' . $analyst->profile_last_name, "expert/" . $data->field_analyst_uid, NULL,NULL,NULL,NULL,FALSE) );

    $row[] = array('data' => $analyst->profile_organization);

    $row[] = array('data' => $visitor_team_tick, 'align' => 'center');

    $row[] = array('data' => $home_team_tick, 'align' => 'center');

    $row[] = array('data' => $data->field_line_value);

    $row[] = array('data' => $visitor_team_tick_line, 'align' => 'center');

    $row[] = array('data' => $home_team_tick_line, 'align' => 'center');

    

    

    $rows[] = $row;    

  }

  drupal_add_js($directory . '/jquery.tablesorter.min.js');      

  drupal_add_js($directory . '/my_table_sort.js');  



  $output .= '<br>' . t('Click on column headers to sort table');

  $output.= theme("table", $header, $rows, array('id' => 'game-all-picks', 'class' => 'tablesorter'));

  print theme("page", $output);

}



function propicks_quickpicks_page() {

  $output = '';

  //display all the games for the week with ranking etc

  $games_nid = propicks_get_this_week_games();

  //print_r($games_nid);

  $output .= '<div class = "h2">' . t('Office Pool Quick Picks') . '</div>';

  $output .= '<div class="quickpicks-page-box">';

  $output .=

    '<div class= "quickpicks-page-quickpicks-box-row">' . 

      '<div class="quickpicks-page-quickpicks-box-date">' . t('DATE') . '</div>' . 

      '<div class="quickpicks-page-quickpicks-box-team1">' . t('GAME') . '</div>' . 

      '<div class="quickpicks-page-quickpicks-box-pick">' . t('PICK') . '</div>' . 

      '<div class="quickpicks-page-quickpicks-box-confidence">' . t('CONFIDENCE') . '</div>' .

      '</div>';

      

  foreach ($games_nid as $game) {

    $pick_stats_straight = propicks_get_game_straight_picks_percentage($game['nid']);  

    

    if ($pick_stats_straight['percentage'] == -1) {

      $pick = 'N/A';

    }

    elseif ($pick_stats_straight['percentage'] >= 50) {

      $pick = propicks_get_team_abbr($game['team2']);

    }

    else {

      $pick = propicks_get_team_abbr($game['team1']);

    }



    if ($pick_stats_straight['percentage'] == -1  ) {

      $confidence = NULL;

    }

    elseif  ($pick_stats_straight['percentage'] > 95 || $pick_stats_straight['percentage'] < 5) {

      $confidence = t('bet the farm');

    }

    elseif  (($pick_stats_straight['percentage'] > 85 && $pick_stats_straight['percentage'] <= 95) || 

          ($pick_stats_straight['percentage'] < 15 && $pick_stats_straight['percentage'] >= 5 )) {

      $confidence = t('almost guaranteed');  

    }

    elseif  (($pick_stats_straight['percentage'] > 70 && $pick_stats_straight['percentage'] <= 85) || 

          ($pick_stats_straight['percentage'] < 30 && $pick_stats_straight['percentage'] >= 15)) {

      $confidence = t('warm & fuzzy');  

    }  

    elseif  (($pick_stats_straight['percentage'] > 60 && $pick_stats_straight['percentage'] <= 70) || 

          ($pick_stats_straight['percentage'] < 40 && $pick_stats_straight['percentage'] >= 30)) {

      $confidence = t('fingers crossed');  

    }  

    elseif  (($pick_stats_straight['percentage'] >= 50 && $pick_stats_straight['percentage'] <= 60) || 

          ($pick_stats_straight['percentage'] < 50 && $pick_stats_straight['percentage'] >= 40 )) {

      $confidence = t('coin toss');  

    }

      $output .= 

        '<div class= "quickpicks-page-quickpicks-box-row">' . 

          '<div class="quickpicks-page-quickpicks-box-date">' . date("D, M j, g:iA", $game['date']) . '</div>' . 

          '<div class="quickpicks-page-quickpicks-box-team1">' . 

          l(propicks_get_team_name($game['team1']) . ' @ ' . propicks_get_team_name($game['team2']), "node/" . $game['nid'], NULL,NULL,NULL,NULL,FALSE) . '</div>' . 

        '<div class="quickpicks-page-quickpicks-box-pick">' . $pick . '</div>' .         

          '<div class="quickpicks-page-quickpicks-box-confidence">' . $confidence . '</div>' .

        '</div>';

  }

  

  $ouput .= '</div">';  

  

  return $output; 

}





function propicks_bettors_breakdown_page() {

  $output = '';

  //display all the games for the week with ranking etc

  $games_nid = propicks_get_this_week_games(time());

  drupal_set_message();

  //print_r($games_nid);

  $output .= '<div class = "h2">' . t('Bettor\'s Breakdown') . '</div>' . '<br />';

  $output .= '<div class="bb-page-table">';

  $output .=

    '<div id="bb-page-header">' . 

      '<div class="bb-page-date">' . t('DATE') . '</div>' . 

      '<div class="bb-page-teams">' . t('GAME') . '</div>' . 

      '<div class="bb-page-line">' . t('AVERAGE SPREAD') . '</div>' . 

      '<div class="bb-page-linepicks">' . t('LINE<br />PICK %') . '</div>' . 

      '<div class="bb-page-num-pick">' . t('NUMBER OF LINE PICKS') . '</div>' . 

      //'<div class="bb-page-linepicks-top10">' . t('TOP 10 EXPERTS') . '</div>' .       

      '<div class="bb-page-record">' . t('RECORD') . '</div>' . 

      '<div class="bb-page-record-home">' . t('RECORD HOME') . '</div>' . 

      '<div class="bb-page-record-away">' . t('RECORD AWAY') . '</div>' . 

      '<div class="bb-page-record-vs-spread">' . t('RECORD VS SPREAD') . '</div>' . 

      '<div class="bb-page-all-picks">' . t('ALL<BR />PICKS') . '</div>' . 

    '</div>';

      

  foreach ($games_nid as $game) {

    $pick_stats = propicks_get_game_all_line_picks_percentage($game['nid']);  

    $team1_record = propicks_get_team_record($game['team1']);

    $team2_record = propicks_get_team_record($game['team2']);

    if ($pick_stats['percentage'] == -1) {

      $team1_percentage = '--';

      $team2_percentage = '--';

    }

    else {

      $team1_percentage = (100 - $pick_stats['percentage']) . '%';

      $team2_percentage = $pick_stats['percentage'] . '%';    

    }

    if (propicks_get_game_average_line($game['nid']) == NULL) {

      $average_spread1 = '--';

      $average_spread2 = '--';

    }

    else {

      $average_spread1 = propicks_number_with_sign( - propicks_get_game_average_line($game['nid']));

      $average_spread2 = propicks_number_with_sign(propicks_get_game_average_line($game['nid']));

    }

    

    $output .= 

      '<div id="bb-page-row">' . 

        '<div class="bb-page-date">' . date("D, M j", $game['date']) . '</div>' .          

        '<div class="bb-page-teams">' . l(propicks_get_team_name($game['team1']), "node/" . $game['nid'], NULL,NULL,NULL,NULL,FALSE) . '</div>' . 

        '<div class="bb-page-line">' . $average_spread1 . '</div>' . 

        '<div class="bb-page-linepicks">' . $team1_percentage  . '</div>' . 

        '<div class="bb-page-num-pick">' . ($pick_stats['num_picks']  - $pick_stats['team2']) . '</div>' . 

        //'<div class="bb-page-linepicks-top10">' . '--' . '</div>' .         

        '<div class="bb-page-record">' . $team1_record['win_total_straight'] . '-' . $team1_record['loss_total_straight'] . '</div>' . 

        '<div class="bb-page-record-home">' . $team1_record['win_home_straight'] . '-' . $team1_record['loss_home_straight'] . '</div>' . 

        '<div class="bb-page-record-away">' . $team1_record['win_away_straight'] . '-' . $team1_record['loss_away_straight']  . '</div>' . 

        '<div class="bb-page-record-vs-spread">' . $team1_record['win_total_vs_spread'] . '-' . $team1_record['loss_total_vs_spread']  . '-' . $team1_record['tie_total'] . '</div>' .         



        '<br class="clearfloat"/>' . 



      '<div class="bb-page-date">' . date("g:iA", $game['date']) . '</div>' .          

        '<div class="bb-page-teams">' . 'at ' . l(propicks_get_team_name($game['team2']), "node/" . $game['nid'], NULL,NULL,NULL,NULL,FALSE) . '</div>' . 

        '<div class="bb-page-line">' . $average_spread2 . '</div>' . 

        '<div class="bb-page-linepicks">' . $team2_percentage . '</div>' . 

        '<div class="bb-page-num-pick">' . $pick_stats['team2'] . '</div>' . 

        //'<div class="bb-page-linepicks-top10">' . '--' . '</div>' .         

       '<div class="bb-page-record">' . $team2_record['win_total_straight'] . '-' . $team2_record['loss_total_straight'] . '</div>' . 

       '<div class="bb-page-record-home">' . $team2_record['win_home_straight'] . '-' . $team2_record['loss_home_straight'] . '</div>' . 

        '<div class="bb-page-record-away">' . $team2_record['win_away_straight'] . '-' . $team2_record['loss_away_straight']  . '</div>' . 

        '<div class="bb-page-record-vs-spread">' . $team2_record['win_total_vs_spread'] . '-' . $team2_record['loss_total_vs_spread']  . '-' . $team1_record['tie_total'] . '</div>' .     

        '<div class="bb-page-all-picks">' .  l('more...' , "all-picks-for-game/" . $game['nid'], NULL,NULL,NULL,NULL,FALSE). '</div>' .

      '</div>';

  }

  

  $ouput .= '</div">';  

  

  return $output; 

}



function propicks_get_team_record($nid) {

  // get all games for a given team and 

  // get average score for each game

  //determine win/loss

  $sql = "SELECT g.nid, field_score1_value, field_score2_value

    FROM {content_type_game} g

    WHERE field_team_2_nid = %d

    AND field_score1_value IS NOT NULL

    AND field_score2_value IS NOT NULL

    ";

  $result = db_query($sql, $nid);

  $stats_total['tie'] = 0;

  $home['win_straight'] = 0;

  $home['loss_straight'] = 0;

  $home['win_vs_spread'] = 0;

  $home['loss_vs_spread'] = 0;



  while($data = db_fetch_object($result)) {

    if ($data->field_score2_value > $data->field_score1_value) {

      $home['win_straight'] += 1;

    }

    else {

      $home['loss_straight'] += 1;

    }



    if ($data->field_score2_value + propicks_get_game_average_line($data->nid) > $data->field_score1_value) {

      $home['win_vs_spread'] += 1;

    }

    elseif ($data->field_score2_value + propicks_get_game_average_line($data->nid) < $data->field_score1_value) {

      $home['loss_vs_spread'] += 1;

    }

    else {

      $stats_total['tie'] += 1;

    }

    

  }



  $sql = "SELECT g.nid, field_score1_value, field_score2_value

    FROM {content_type_game} g

    WHERE field_team_1_nid = %d

    AND field_score1_value IS NOT NULL

    AND field_score2_value IS NOT NULL    

    ";

  $result = db_query($sql, $nid);

  $away['win_straight'] = 0;

  $away['loss_straight'] = 0;

  $away['win_vs_spread'] = 0;

  $away['loss_vs_spread'] = 0;

  

  while($data = db_fetch_object($result)) {

    if ($data->field_score1_value > $data->field_score2_value) {

      $away['win_straight'] += 1;

    }

    else {

      $away['loss_straight'] += 1;

    }



    if ($data->field_score1_value - propicks_get_game_average_line($data->nid) > $data->field_score2_value) {

      $away['win_vs_spread'] += 1;

    }

    elseif ($data->field_score1_value - propicks_get_game_average_line($data->nid) < $data->field_score2_value) {

      $away['loss_vs_spread'] += 1;

    }

    else {

      $stats_total['tie'] += 1;

    }



    

  }

  

  return array(

    'win_home_straight' => $home['win_straight'],

    'loss_home_straight' => $home['loss_straight'],

    //'win_home_vs_spread' => $home['win_vs_spread'],

    //'loss_home_vs_spread' => $home['loss_vs_spread'],

    

    'win_away_straight' => $away['win_straight'],

    'loss_away_straight' => $away['loss_straight'],

    //'win_away_vs_spread' => $away['win_vs_spread'],

    //'loss_away_vs_spread' => $away['loss_vs_spread'],

    'win_total_straight' => $home['win_straight'] + $away['win_straight'],

    'loss_total_straight' => $home['loss_straight'] + $away['loss_straight'],

    'win_total_vs_spread' => $home['win_vs_spread'] + $away['win_vs_spread'],

    'loss_total_vs_spread' => $home['loss_vs_spread'] + $away['loss_vs_spread'],

    'tie_total' => $stats_total['tie']



  );







}





function propicks_get_this_week_games($datestamp = NULL) {

  if ($datestamp == NULL) {

    if (date("l", time()) != 'Wednesday') {

      $begin_timestamp = strtotime('+1 day', strtotime("last Wednesday", time()));

    }

    else {

      $begin_timestamp = strtotime('+1 day', time());

    }

  }

  else {

   if (date("l", $datestamp) != 'Wednesday') {

      $begin_timestamp = strtotime('+1 days', strtotime("last Wednesday", $datestamp));

    }

    else {

      $begin_timestamp = strtotime('+1 days', $datestamp);

    }

  }

  

  $end_timestamp = $begin_timestamp + 604799;                      

  $games = array(); 

  $sql = "SELECT g.field_datestamp_0_value, g.nid, g.field_team_1_nid, g.field_team_2_nid

    FROM {content_type_game} g ORDER BY nid";

  $result = db_query($sql, $begin_timestamp, $end_timestamp);

  while($data = db_fetch_object($result)) {

    if ($data->field_datestamp_0_value >= $begin_timestamp && $data->field_datestamp_0_value <= $end_timestamp) {

      //drupal_set_message(date("d M Y", strtotime($data->field_date_value)));    

      $games[] = array(

        'date' => $data->field_datestamp_0_value, 

        'nid' => $data->nid,

        'team1' => $data->field_team_1_nid,

        'team2' => $data->field_team_2_nid,

      );

    }

  }

  

  return $games;

      

}



function propicks_get_team_name($team_id) {

  return db_result(db_query("SELECT n.title FROM {node} n WHERE nid = %d", $team_id));

}



function propicks_get_team_abbr($team_id) {

  return db_result(db_query("SELECT n.field_abbr_value FROM {content_type_team} n WHERE nid = %d", $team_id));

}